hi all, i am a newbie, any kind heart help me? Plese, please

[xiaodao]

i am a newbie, very new, but i am requested by my boss to do an impossible job. i need to created a form with input box of date, timefrom, timeto and last vehicle number?
when somebody enter the values, the form need to perform the check on vehicle number then on date, last on timefrom and timeto, if there is already a record in database, an errror will prompt, but if there is not, the record is entered.
i have tried to create the form and table, but i dont know how to write queries to make it work, can any kind heart help me out of this poor stage(i already drag the thing for almost two months, if going on , i will lose my job. please)

 

[richary]

Personaly I would use VB to do what you require.

You can use the BeforeUpdate event for your Vehicle number field : something along the lines of:

Private Sub VehicleNumber_BeforeUpdate(cancel As Integer)

If (Not IsNull(DLookup("[VehicleNumber]", "Your_Table_Name", "[VehicleNumber] =" & "'" & Me.VehicleNumber & "'"))) Then
MsgBox "The Vehicle has already been entered into the database"
cancel = True
Me.VehicleNumber.Undo
End If

End Sub

Hope it helps

Richard

 

[neileg]

Richard's answer gets you going on the vehicle number. You need to think about the date and time bit. You can use similar techniques to test for exact matches, but what about overlaps?

Say you have a vehicle that is in the data already for 20 Dec 04 11:00 to 15:00. How do you want to deal with a new piece of data that is 20 Dec 04 12:00 to 16:00? This is not a simple situation to deal with!

 

[aleb]

I used a composite key for this look in design of the form

 

[xiaodao]

Richard's answer gets you going on the vehicle number. You need to think about the date and time bit. You can use similar techniques to test for exact matches, but what about overlaps?

Say you have a vehicle that is in the data already for 20 Dec 04 11:00 to 15:00. How do you want to deal with a new piece of data that is 20 Dec 04 12:00 to 16:00? This is not a simple situation to deal with!

even overlap still consider false, by the way, my boss insist on using access, what can i do, i cannot argue, thanks guys for help, i will try your method, hope you can help me on the way, the access system they give me to use is 97, sigh

 

[neileg]

the access system they give me to use is 97, sigh

Nothing wrong with Acess 97. Probably better than 2000. It produces smaller files, too.

 

[richary]

Access 97 is capable of doing what you require, no problem.
The zipfile is aleb's database converted to Acc-97, and uses the composite key method as (s)he describes. I have added my sub to the form's 'before update' event to do the checking. I've given it a quick try, seems to work OK, but you will have to examine further to make sure that it meet your needs - and those of the users.

One thing that you may want to look as is the composite key method as it stands depends on the local machine's default date time format. As long as the db is always used on a single machine (or used on different machines with same date format), should be no problem.

I'm sure your boss will thank you for your efforts !

 

[aleb]

Here is db that checks as well if there is an overlap,

Thank you Pat,
The first time I saw the "composite key" was in JDEdwards which is not a relational dbase but I thought that this is the common pratice. Pat, do you have any good examples of composite key usage?

Best regards Alexei ( he )