Multiple Commands in an If Statement

[spock1971]

Can anyone help please?

I have used dcount to check the number of records for a query. If there is more than one matching record then a window pops up allowing the user to identify the correct record before proceeding. If there is only one record then the front end form opens with the data populated.

However, when multiple records are found my form still opens but blank.

I have:

if dcount(etc) >1 then openquery
if dcount(etc) <2 then set parameter value
openform
exit sub
end if

I've tried loads of alternatives but either get the form not opening when there is one record or it opens with the first found record.

Any ideas how best to split it or force access to only open form when dcount <2

Cheers

 

[gemma-the-husky]

im not sure - the logic may be suspect - with end ifs included this is what you are doing

if dcount(etc) >1 then 
      openquery
      'what does this do - do you need to exit sub here?
end if

if dcount(etc) <2 then 
      'ie if 0 or 1
      set parameter value
end if

'therefore the form may be opening with a dcount of 0 - hence empty form
openform
exit sub

 'i'm not sure where the matching if is for this, because the if..then
 'on single lines stand on their own
end if

 

[spock1971]

Thanks for replying Gemma.

Yeah I guess we can exit the sub aftre the first dcount but it appears to be doing that anyway.

My code now is:

Dim stdocname As String
If knownno <> "" Then
Dim int2 As Integer
int2 = DCount("[property]", "property", "[known_as] Like '*" & [Forms]![Navigation].[knownno] & "*'")
If int2 > 1 Then DoCmd.OpenQuery "addresschoice", acViewNormal, acReadOnly
Exit Sub
End If

If knownno <> "" Then
int2 = DCount("[property]", "property", "[known_as] Like '*" & [Forms]![Navigation].[knownno] & "*'")
If int2 < 2 Then Forms!Navigation.EPOSNo = DLookup("[property]", "property", "[known_as] Like '*" & [Forms]![Navigation].[knownno] & "*'")
DoCmd.OpenForm cboChoice, acNormal
Exit Sub
End If

So basically - allow a text field search and if there's more than one match show the query window for the user to pick the correct record. However, if there's only one then open the form and populate it is my intention.

I have got the if 'knownno' bit in there because otherwise access doesn't allow me to do an end if. I appear to have to have 2 if's to 1 endif.

 

[boblarson]

I have got the if 'knownno' bit in there because otherwise access doesn't allow me to do an end if. I appear to have to have 2 if's to 1 endif.

Actually, you only do have one End If to two If's in each part because these lines:

If int2 > 1 Then DoCmd.OpenQuery "addresschoice", acViewNormal, acReadOnly

If int2 < 2 Then Forms!Navigation.EPOSNo = DLookup("[property]", "property", "[known_as] Like '*" & [Forms]![Navigation].[knownno] & "*'")

Do not need End If's because they are self contained on one line. Any IF on a single line with it's Then doesn't need an End IF.

 

[CraigDolphin]

I think the problem comes about because you are opening a query, then not returning the id of the selected record to the EPOSNo control on your nav form before opening up the cmboChoice form.

I've replaced the confusing ifs with a select case statement and annotated the code with what I think is going on.

Dim stdocname As String
Dim int2 As Integer

If knownno <> "" Then
    int2 = DCount("[property]", "property", "[known_as] Like '*" & [Forms]![Navigation].[knownno] & "*'")
    
    Select Case int2
    
    Case 0
    'no matching records so do nothing?
    MsgBox "Sorry, no matching record found!", vbInformation
    
    Case 1
    'this bit sets the parameter for the form to open with when only one record is found
    Forms!Navigation.EPOSNo = DLookup("[property]", "property", "[known_as] Like '*" & [Forms]![Navigation].[knownno] & "*'")
    'here the form opens, presumably using the EPOSNo control as a parameter
    DoCmd.OpenForm cboChoice, acNormal
    
    Case Is > 1
    'this is the bit where you are having trouble.
    'you are opening a query to let the user "select" a record
    'but how dows this code know which record is selected by the user? 
 
    
    'here the query opens
    'Remember the code keeps running at this point
    DoCmd.OpenQuery "addresschoice", acViewNormal, acReadOnly

    'the code continues to run as the query opens and here the form opens,
    'presumably using the EPOSNo control as a parameter, but we haven't 
    'set this yet!
    DoCmd.OpenForm cboChoice, acNormal
    
    Case Else
    'just in case something weird happens
    MsgBox "WTF?", vbQuestion
    
    End Select

End If

I think the solution is to replace the line where the query opens, with code that opens a popup 'selection' form (bound to your query) in multiple form view. This form needs to be opened in dialog mode so that the code pauses until the selection form is closed. When the user selects the record to use (maybe via an unbound checkbox), use the after update event of the checkbox to set the value of the EPOSNo control on your navigation form to whatever the id of the selected record is, then close the selection form.

Then the code continues to run and your cmboChoice form opens.

 

[Simon_MT]

Personally, I would present the information in a subform and users can simply Select which record they want. If there is no records - nothing happens. Ifyou apply a consistant approach to the UI users get so used method of handling multiple records, clicking to select to records becomes second nature.

Simon

 

[spock1971]

Thanks all. This appears exactly what I need at first glance. Will put it in and see how it goes.

Thanks again.

 

[spock1971]

Ok that's working great.

I was originally planning to show the results of the query and the user make a mantal note of the eposno and then enter it manually on the navigation form. However, your checkbox idea sounds a much better prospect.

How do I do that then. I tried putting a checkbox ontop of the property field in subform which gave me a box for every returned record but once I click on any one of them they all get ticks.

Also, how do I then send the chosen eposno back to the navigation form.

Cheers in anticipation

 

[CraigDolphin]

On second thought, forget the checkbox. Simply use the click event of the property field. Make sure you have the EPOSNo field on the form as well (it can be invisible if you like). Then use something like:

Private Sub Property_Click()
Forms!Navigation.EPOSNo = Me.EPOSNo 'use the name of the control bound to the EPOSNo field on your popup or subform
DoCmd.Close 'use this if you're using popup, or...
'DoCmd.OpenForm cboChoice, acNormal 'use this if you're using a subform
End Sub

Bear in mind, my code was written with the intention of using a popup form not a subform. The reason for this is that by opening a popup form in dialog mode you can pause the execution of the code until the popup form closes.

If you are using a subform instead of a popup form then you will need to delete the 'DoCmd.OpenForm cboChoice, acNormal' line of code in the case >1 statement, and replace the DoCmd.Close line at the end of the click event with it.

 

[spock1971]

Excellent - had decided to try that route and got it working.

Good to hear I'm thinking like the experts tho.

Cheers again

 

[CraigDolphin]

Glad to help