form problem

[cgmckenzie]

I have a form with a combo box on it that pulls information from a table called customers. The combo box pulls information such as company name, address, phone, and etc. In the customer table I have a field for cargo insurance and liability insurance. The validation rule for the cargo, liability, and Hazardous material insurance field is ">Now()" . What I need the program to do is when I pick a company from the combo box if thier insurance is expired I need it to show an error message like cargo are liability insurance has expired. I quess what the program needs to do is evaluate the validation of the data in the insurance fields when I pick a particular trucking company from the combo box....
Thanks for your help This website is a great source of help Thankyou Chris

 

[Jack Cowley]

In the On Current event of the form:

If Me.MyField > Date() Then
MsgBox "Ins. has expired"
End if

hth,
Jack

 

[Rich]

Shouldn't it be <Date() ?

 

[cgmckenzie]

I am sorry I meant to right ">Date()"

 

[cgmckenzie]

Jack will it still work if the ins in not a field on the form.

 

[Jack Cowley]

Nope. Can you add the field to the form? If not, then use DLookup() in the On Current event to get the date and compare the date you find to todays date....

hth,
Jack

 

[cgmckenzie]

Jack, If I add the cargo, haz-mat and liability insurance fields to the combo box with the address, phone, number, and etc. Would that work and if not how would I add these fields to the form.Thanks

 

[Jack Cowley]

Let's start at the beginning... What is the purpose of the combo box? What is the purpose of the form that combo box is on?

Jack

 

[cgmckenzie]

The form is a truckload form that goes to a table called Truckloads. The combo box pulls the trucking company that is going to haul the load. When you select a particular trucking company from the combo box the adress,phone,fax,and other information about the trucking company fills in. The information comes from a table called TruckCompany. Since a lot of time can expire between a trucking company hauling one load and the next load when I select the trucking company from the combo box I need the form to tell me if any of their insurance has expired. Thanks Chris Mckenzie

 

[Jack Cowley]

Chris -

Gotcha! Thanks for the explanation. Here is how to check the date. Add the field that has the insurance expiration date to the combo box. Lets say that it ends up in the 6th physical column of the combo box. Put code like this in the After Update event of the combo box:

If CDate(Me.ComboBoxName.Column(5)) < Date Then
MsgBox "Expired Insurance"
End if

Remember that combo boxes are zero based and that is why we refer to column 5 if the data is in column 6.

hth,
Jack

 

[cgmckenzie]

Hey, that worked for one of the fields, I put it in twice once for the liability and once for the cargo. The cargo worked good which was the first one but the liability has an error. should i combine the statements somehow thank you for your time

 

[Jack Cowley]

Do you have two date fields that you want to check? If so, what if one is current and the other is not, what do you do then? Do you want to check these two fields together or independently?

What did your second code look like and what error did it give you?

Jack

 

[cgmckenzie]

Hey, the fields are independent and have seperate expiration dates. This is the way i put the code in

If CDate(Me.TruckCo.Column(10)) < Date Then
MsgBox "Cargo has Expired"
End If
If CDate(Me.TruckCo.Column(11) < Date Then
MsgBox "Liability has Expired"
End If


The cargo part works fine the Liability causes an error message
Run time error '94'
Invalid use of Null

That is all it says
Thanks for your help.......

 

[Jack Cowley]

Is the missing parenthesis in this line of code just a typo in your post?

If CDate(Me.TruckCo.Column(11) < Date Then

Is the date in column 11 just a date only (no time included)? Can the date be missing from the date field in column 11? Does the code ALWAYS fail? Are you sure that you have the Column Count property set to 11?

Sorry, more questions and no solution to offer you yet....

Jack

 

[cgmckenzie]

that was just a typo on my reply. Both fields are just dates, no time included, the format is set to short date. Both fields are set to Required. The column count is correct the cargo works by itself but when i add the liability code it fails every time. Thanks again for your help..

 

[Jack Cowley]

Would it be possible to create a demo from your db to post here so I can take a look at what you are doing? If the db is not to big you could post it after zipping it. Without seeing what is happening I am out of ideas on why you are getting this error.

One last thing. Have you stepped through the code while it is executing to see what line is causing error 94? Knowing that could help track down the problem.

Let me know what you want to do and we can go from there.

Jack

 

[cgmckenzie]

Jack i don't have any data in the data base pretty much blank but it keeps saying it is to big to send, i am using winzip but if you think of another way to look at it let me know and i will send it to you thanks for your time

 

[Poot]

Compact

I had the same problem when trying to post a bare-bones db.

Goto:

Tools > Database Utilities > Compact and Repair Database

and go for that... squidged mine down to a nice size that I could .zip and post on here.

HTH.

Poot.

 

[Jack Cowley]

If doing the compacting per Poot's instructions still left it too large to post here you can email it to me directly. No guarantees that I can solve the problem, but I will take a look.

You say there is little or no data, but when you were testing your code you did have data in the fields that you were checking, right? If you had no data then you will get the invalid use of null error....

Jack

 

[cgmckenzie]

Jack it was still to large. there is about 3 records in each table just to check the data. Where can I send it. Thanks again for the help and your time........