Replies

Quick Example:

public sub Math(Value1)
dim Problem as int

select case Value1
case is = “-”
Problem = 5 – 5
case is = “+”
Problem 5 + 5
end select
msg box “Problem”
end sub.

Then you set two buttons, one says On Click Math “+”, and one says Math “-”

When you click the buttons, depending on which one you click it will give you a message box with either 0 or 10.

jeff

Set fld = TDFTest.CreateField(qdfField.Fields(2).NAME, dbText, 11)
fld.DefaultValue = 0
TDFTest.Fields.Append fld

HEY MOE! IT WOIKS! Nyuk Nyuk Nyuk.

Thanks much.

It would be a simple matter to have two fields, one called Member and one called Trial_Date. The “Member” field would be a yes/no, the other would be the date the person was interested in finding out about/getting the mailing. When a person trying the Trial Membership out became a full fledged member then you click the “Member” field. For your mailing report you would query where “Member” = 0 AND “Trial_Date” > date() – 30 OR where “Member” = -1

This way you have combined the two tables and keep your mailing list on one table. You could even run a delete query every quarter that deletes all records where member = 0 and trial_date < Date() – 30.

Mark,

Another suggestion for some of your clients (depending on the availability of broadband internet access) is MS NetMeeting. I frequently use it from Louisville, KY to converse/work with a friend in Anchorage Alaska. We are both on dial-up, but it meets our needs (at 1am). Just a thought.

Jeff

Oops: Coupla’ corrections:

I did not read Charlottes response properly. It very well may be that the shell command bombs when there are spaces in the folder names. I’ve never run into that problem (i.e. all of my paths thus far have been one word).

I misspelled otherwise.

Good Luck
Jeff

D,

I do this all of the time. I have an ever-evolving database that constantly requires modification. To fix this, however, I’ve created a module with versioning logic in my database. I have a table on my server side called TblVersion, and a client-side table called TblVersionSlave. Copy the following text and save it as a .bat

@echo off
cls
echo.
echo Preparing to copy Updated Linked Database to your Computer for the DatabaseName
pause
echo This may take a moment, please be patient.
echo.
COPY “X:Path1Path2DatabaseName.mdb” “C:WINDOWSDesktopDatabaseName.mdb”
COPY “X:Path1Path2UpdateDb.bat” “C:WINDOWSDesktopUpdateDb.bat”
pause
:cd
cls
echo Update Completed see DatabaseName Icon on WIN95 Desktop
echo Use this Icon to access Database Name
echo.
echo

echo Click X to Exit…

See the attached file for the actual versioning logic.
The module will compare the linked table version with the local table version. If the version listed on the linked table is greater than the version on the local table then the module shells out to DOS and launches the batch file, then quits the Access app to allow a copy over.

Whenever you do an update to your database you log into the server table the changes, and log the identical record into the local table. You put your updated copy on the server that everyone can download from and it will update the database whenever you release a new version. Be sure to put the updated version record on the tblVersionSlave, otherwise your users will get a constant prompt to update. If you have any questions feel free to contact me at CMOTDever@aol.com

Jeff

PS, A few things: Charlotte is right about the spaces in the names of folders with a batch file UNLESS the path and file name are included in double “quotes”, and for this to work (as I have coded it) you need to put the batch file on their Win95 desktop. (Note-this only works with 9x, not with NT due to the path structure for the desktop. You could modify it to suit your needs, however I have never had to). You will need a Zip program to open the file I have attached. It’s 102k so I could not upload it otherwiese. Sorry.

You could create a table of all users and there level of security (For my example the table is called “TblUsers” and has the User’s name field called UserName, and the Level of Security Field called “SLev”). Create a module for routing that says something to the effect of:

Public Sub MenuRouting()

Dim Rst1 as recordset
Dim MyDb as database
Dim strSQL as string

strSQL = “Select * from TblUsers where UserName = CurrentUser()”

set MyDb = CurrentDb
set Rst1 = MyDb.openrecordset(strSQL,dbopendynaset,dbseechanges)

select case Rst1!Slev
case is = “User”
docmd.openform “frmUserMenu”
case is = “Admin”
docmd.openform “frmAdminMenu”
end select

docmd.closeform “frmSplashScreen”

set Rst1 = Nothing
set MyDb = Nothing

end sub

You set up a “Splash Screen” as your startup. The splash screen would have a label with your name and number/email address and a note that for support they should contact you. Put a 2 second (or less) timer event on the form. The Timer Event would be MenuRouting() which would evaluate the level of the user, then open the appropriate menu form designed for that level.

It could possibly be done a bit cleaner than this, but this should get you what you need.

You could always put a message box in the error trapping of the before update event of the form that said,

if msgbox(“You have had an error trying to save this record. Perhaps you’ve already entered your report for this day. Would you like to search for your entry for today?”,vbyesno) = vbyes then
docmd.runcommand accmdfilterbyform
docmd.runcommand accmdcleargrid
end if

This would prompt them with a yes/no question. If they answer yes then they will go into a filter by form so that they could look up their entry for that day. If you wanted to, you could also include some code to copy the “comments section” of the form so that they could hit CTRL V and paste the additional comments onto the end of the existing report.

Jeff Dever

Oops. Access 97 SR2 on Win NT 4.0

I frequently find that I can create a form that is identical to a basic report. If you don’t have too complex a report, go into design mode, then copy all of the controls on the form. Open a blank form and paste the controls. On this you can place any kind of button you want.

Jeff

The only way I could see to do this would be to add an autonumber to the table, then require a “Save” button to be clicked to save the record. The save would save the record (thus creating the record number on the autonumber field) then it would

YearRecNum = right(date(),4) & txtAutonum
docmd.runcommand.accmdrefresh

where YearRecNum is the field concatenating the Autonumber and the Year off of the date field. For this to work the regional settings MUST have the date set to mm/dd/yyyy.

This, however, may not solve your problem. My thoughts are that your user wants to be able to look at a record and say “This is the 135th Account for 2001.”, which may not be the case. An autonumber field will not use the same number twice, so if a record was added by mistake then deleted, the numbering system is then off. Likewise, it will not reset 1-1-2002. You might want to verify the user’s goal in establishing this key. Then again I might be entirely off, and the above solution could be exactly what you want.

Hope this helps.

Jeff

(solution was for an Access 97 SR2 configuration)

The following code will update the DOB (date of birth) field on a table called tbldemographics, where the DOB is greater than today. Your regional settings MUST have the date set to mm/dd/yyyy for this to work. Make a copy of the database, then run this against the copy. NEVER RUN ANY UN-TESTED UPDATES AGAINST PRODUCTION. (Sorry for shouting, I just can’t say that enough. I’m on Access 97 SR2.

jeff

UPDATE TblDemographics SET TblDemographics.DOB = Left([TblDemographics]![DOB],6) & “19” & Right([tblDemographics]![dob],2)
WHERE (((TblDemographics.DOB)>Date()));

ps, some wise-acre out there is probably going to point out to you that the 19th century starts 1801, the 20th century starts 1901, and the 21st century starts 2001. Alas, some wise-acre has.

In retrospect I don’t think I made that clear. I have two tables that get the exact same data copied on to them. One table resides on the front-end (of a split database) the other resides on the back-end. The module compares the two tables, if the back-end has a newer version, then it copies the latest copy of the front-end down from the server.

sorry ’bout that.

I have done the exact same thing except I have a module that is called on the on open event of the startup form. When the form opens it compares the version of the copy on my desktop to the version listed in backend (be that SQL Server or Access on a network drive). It sends up a message box with the notification, and the comments section. This allows my users to know that there is a newer version available. I have a DOS batch file that copies the front-end down from the network drive. I can either let the batch file be executed by the user or call it from the module (it depends on my users/requirements whether I automatically kick down the upgrade or not).

Let me see if I understand: (My example) You have a form for “Sales”. This form has a combo box for “Item”, which will select a list of items that can be sold. When you select “Snow Shovel”, then it populates the form with the snow shovel information. Is my understanding correct? If so I may be able to help you.