help writing a code

[icemonster]

can someone tell me what needs to be changed here.

basically, what i want is, if the form is opened through the listbox, it would filter the clientID through the listbox, but if the form is a new record, the sql statement would then be filtered using the forms clientid, here's the code.

If Not IsNull(Forms![frmMainMenu]![lstPatients]) Then
    lngClientID = Forms![frmMainMenu]![lstPatients]
    'determine if there is any existing value in the "strwheresql" variable
        strWhereSql = "Where qryClientStatusList1.CSCLIENTID = " & lngClientID & " "
    Else
        strWhereSql = ""
End If

If Not IsNull(Forms![frmClientDetails1]![txtClientID]) Then
    lngClientID2 = Forms![frmClientDetails1]![txtClientID]
If strWhereSql = "" Then
        strWhereSql = "Where qryClientStatusList1.CSCLIENTID = " & lngClientID2 & " "
    Else
       strWhereSql = strWhereSql & "And qryClientStatusList1.CSCLIENTID = " & lngClientID2 & " "
    End If
End If

 

[GinaWhipp]

You didn't mention what version of Access... I would use the IsLoaded() function...

If IsLoaded(Forms![frmMainMenu]![lstPatients]) Then
lngClientID = Forms![frmMainMenu]![lstPatients]
Else
lngClientID = Forms![frmClientDetails1]![txtClientID]
End If

However, if there is more then two forms then perhaps a SELECT CASE statement?

 

[icemonster]

getting an error, it wants me to define it?

 

[boblarson]

To use the built-in IsLoaded function:

If CurrentProject.AllForms("frmMainMenu").IsLoaded Then

 

[icemonster]

so it would look like this?

If CurrentProject.AllForms("frmMainMenu").IsLoaded Then
    If IsLoaded(Forms![frmMainMenu]![lstPatients]) Then
        strWhereSql = "WHERE qryClientStatusList1.CSCLIENTID = " & Forms![frmMainMenu]![lstPatients] & " "
    Else
    strWhereSql = "WHERE qryClientStatusList1.CSCLIENTID = " & Forms![frmClientDetails1]![txtClientID] & " "
    End If
End If

is there any other alternative to open a form with two criteria? 1 being that if the form is opened through a listbox and the other if the form is just opened through a button (as a new record)?

 

[GinaWhipp]

Oops, let's fix that...

If CurrentProject.AllForms("frmMainMenu").IsLoaded Then
    strWhereSql = "WHERE qryClientStatusList1.CSCLIENTID = " & Forms![frmMainMenu]![lstPatients] & " "
Else
    strWhereSql = "WHERE qryClientStatusList1.CSCLIENTID = " & Forms![frmClientDetails1]![txtClientID] & " "
    End If

There are other ways to open a form with multiple criteria but all require code. Is there a reason you don't want to do it this way? Like more then two criteria?