Where Condition - form error

[catat]

Hello..



This database is set up so that before any forms are opened on the main page, a Family must be searched/selected first. I had used the Where Condition ="[FuID]=" & [FuID] so that when the form opens, records for that particular family are filtered. This works great... but if there is no family selected and you try to open one of the forms, an error message appears.. " Syntax error (missing operator) in query expression '[FuID]='. How do I still use the above where condition, but also ask, if there is no FuID selected, open the form blank? I am not familiar with code and was fortunate to have someone help with the Where Condition I am currently using.

Anyways, thanks in advance...

[jzwp11]

...Family must be searched/selected first

How does one select the family? Do you have a form with a combo box where the user makes the selection prior to opening the main form? Is there a button on that preliminary form as well?

[Vera]

I suggest you do a check first to see if something is selected.

If isnull([FuID]) then
open a blank form
else
open the form with the filter for that FuID
end if

[catat]

Thanks for the reply..
I have one main form where the family name is entered in a combo box and a search button is pressed. The Family Unit ID (FuID) and Family Name (FamName) are shown below the search box. When a family is searched, The FuID and FamName appear. All of the forms are linked to the FuID. If a family has not been searched, the forms will have the error message described in my earlier post when opened.

I think what Vera has suggested is what I am looking for but the Where condition is built into the button used to open a form (embedded macro) and I don't know how to enter that information into the macro. I hope I am making sense!

[Vera]

I wouldn't use a macro, but code.
If you put on on click event on the button, you can use the where clause in the code when you want to open the form with the filtered record. And no where clause when you want to open a blank form.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stlinkcriteria As String
stlinkcriteria = ""

If isnull([Forms]![formname with field FuID]![FUID]) then
' open blank document
stDocName = "BlankDocument"
DoCmd.OpenForm stDocName
Else
stlinkcriteria = " [FUID] = " & [Forms]![formname with field FuID]![FUID]
stDocName = "FilledDocument"
' open document with selected FUID
DoCmd.OpenForm stDocName, , , stlinkcriteria
end if

Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

[catat]

Thanks Vera! That is exactly what I was asking for. It works fine.. The only thing is that with a macro I was able to set it up so the form was in Edit Data Mode, but with a new blank record displayed. All records available would still be linked to the one Family Unit chosen, but the form would open a new record, and if the user wishes, he/she would just press a previous record button to see the older records for this family unit.