Show error message after query with bad parameters

[funkygoorilla]

Hi,
I have a couple queries that accept their parameters from forms. The ok button to run the query is using VBA. I have one form with three options, one of which is autofilled, one date picker, and on field the user enters. When the user enters in parameters and hits Ok, if the information they put in is not found, the intended form pulls up, but only the tab of the form. Usually the form will pull up, but with blank fields which would tell the user that the parameters were wrong. If there a way the parameter form show a error message or something if the parameters entered by the user are not found? Probably in VBA I am assuming. Or it would even be helpful to get the form to at least open, but as of right now the form leaves the user stuck.
Thanks, FunkyG

[June7]

Could you use a combobox or listbox that would limit user to only valid choices? Could also try setting ValidationRule and ValidationText properties of the textbox. If these not practical, will need VBA IF THEN ELSE code to check the relevent table for the value and if found run code to open form, otherwise show message.

[funkygoorilla]

Hi,
I have one combo box that is limited to selections in it. And one date field. I would like to do this just do avoid any trouble.
Could you give me a clue as to how I can do this with my current If statement?

Private Sub btnOk_Click()
'Code to check for null entry in vehicle form - JF
If IsNull(vehCbo) Or IsNull(begShftDate) Then
MsgBox "You must enter a Vehicle Number and a Beginning Shift Date." _
& vbCrLf & "Please try again.", vbCritical, _
"More information required."
Exit Sub
End If

DoCmd.OpenForm "frmEndShiftInfo", acViewNormal
DoCmd.Close acForm, "frmEndShiftInfoForm"
End Sub

Thanks for any advice.

[June7]

The bugaboo here is the date entry. User can enter any date, regardless of whether or not actually exists in table. You could do DLookup() function on the table to see if a date is associated with that vehicle. Nest inside the first If:

If IsNull(DLookup("datefield","tablename","vehicleID= " & Me.vehCbo)) Then
MsgBox "No such date for this vehicle."
Else
DoCmd.OpenForm ...
End If

[funkygoorilla]

Hi, I think that will give me a result that should suffice. Only I am a little confused as to which parts of my table I put in the code. I know which table to reference, but where do I get the values for vehicleID and datefield. Are they what the fields are called on the form? Or what the fields are called on the table? Is this basically checking to see if the date matches the vehicle entry that the user input into the table?
Thanks again,

[June7]

Use the table/query field names. Domain functions act on tables or queries, not forms or reports. That's why you have to name the table/query target.

[funkygoorilla]

ok here is what I am using;
Private Sub btnOk_Click()
'Code to check for null entry in vehicle form - JF
If IsNull(vehCbo) Or IsNull(begShftDate) Then
MsgBox "You must enter a Vehicle Number and a Beginning Shift Date." _
& vbCrLf & "Please try again.", vbCritical, _
"More information required."

Else
If IsNull(DLookup("Beginning Shift Date", "tblShiftInfo", "Vehicle Number=" & Me.vehCbo)) Then
MsgBox "No such Date for the vehicle."
Exit Sub
End If
DoCmd.OpenForm "frmEndShiftInfo", acViewNormal
DoCmd.Close acForm, "frmEndShiftInfoForm"
End Sub

The problem now is that the button acts like it is not even being clicked?

[June7]

You show field names with spaces. Names with spaces, special characters, punctuation (underscore is exception) or are reserved words must be enclosed in [], which is why I avoid such structures.

If IsNull(DLookup("[Beginning Shift Date]", "tblShiftInfo", "[Vehicle Number]=" & Me.vehCbo)) Then

[funkygoorilla]

Yeah I tried that too, but clicking the Ok button still doesnt do anything. It may help if it would at least error out, but it wont even do that. Now I dont know where to look and see where it is going wrong?

[June7]

Then the code seems okay.
Step debug. Refer to link at bottom of my post for a tutorial on debugging techniques.

[funkygoorilla]

OK, I have tried to de-bug it but I cant find whats wrong. Can you give me a clue?

[June7]

I would have to analyze the project if you want to provide it.

[funkygoorilla]

OK. here is a copy of the DB front end and back end. The forms I need to do this on are frmEndShiftForm. Any help you can provide will be greatly apprecaited!

[June7]

Things I had to do to get working:

1. Can't get any expression in Default property to work. Shows #Type! error and couldn't get it to go away. I had to remove expression for testing. Think I have encountered this before. Will have to find some other way to get the Seniority ID. I also could not get expression to work in any of the form events. Kept getting error message "Invalid use of the . (dot) or ! operator ...". Very weird.

2. Change date textbox Format property to Short Date.

3. The DLookup must search table for the combination of Seniority ID, VehicleID, and Beginning Shift Date.

If IsNull(vehCbo) Or IsNull(begShftDate) Then
    MsgBox "You must enter a Vehicle Number and a Beginning Shift Date." _
        & vbCrLf & "Please try again.", vbCritical, _
    "More information required."
Else
    If IsNull(DLookup("[Beginning Shift ID]", "tblShiftInfo", "[Vehicle Number]=" & Me.vehCbo _
        & " AND [Seniority ID]=" & Me.senId & " AND [Beginning Shift Date]=#" & Me.begShftDate & "#")) Then
        MsgBox "No such Date for the vehicle."
        Exit Sub
    End If
    DoCmd.OpenForm "frmEndShiftInfo", acViewNormal
    DoCmd.Close acForm, "frmEndShiftInfoForm"
End If

Combobox vehCBO is bound to the Vehicle Number field, not VehicleID. The VehicleID field in tblVehicles is not even needed if you are not using this value as foreign key in related tables. Vehicle Number should be primary key. Also, the combobox won't allow typing the Vehicle Number. Another weirdness. Don't think I have ever dealt with a combobox RowSource where the source field was number type. Even though it has only digits, the Vehicle Number isn't really a number, it is an identifier like SSN or phone#. Identifiers are ideally a fixed length string.

[funkygoorilla]

Hi, thanks for checking that out for me. I am going to try and implement your suggestion in a day or so. Thanks again!