Passing data to another form

[david.paton]

I have a list box that filters the search results as you type and I want to be able to select a record from the list and press another button and for the data entry form to appear so I can edit info on that record. The code I have behind the button you click is DoCmd.OpenForm "widow", acNormal, , [WidowID] = Forms![FRM_SearchMulti]![SearchResults] and I know it is not right, but I am not sure what it should be. Could someone help me please?

 

[jdraw]

Can you post a copy of your database with confidential info removed?
With clear instructions of how to get to and experience the issue

Zip format.

 

[sneuberg]

If widow is the name of the form to be opened, FRM_SearchMulti is the name of the form with the listbox, SearchResults is the name of the listbox and the bound column of the listbox relates to WidowID then just put the criteria in quotes and you should be good to go.

DoCmd.OpenForm "widow", acNormal, , [COLOR="Red"]"[/COLOR][WidowID] = Forms![FRM_SearchMulti]![SearchResults][COLOR="red"] "[/COLOR]

should work. At least it works in the test database I attached. I wasn't sure if you could form the criteria without concatenation. It appears you can.

As an aside when you create the criteria this way by using the full reference the data type of WidowID doesn't make any difference. It will work whether it's numeric or text. This is not the case when you concatenate in the form control data. Another benefit is that unescaped single quotes in the data won't cause syntax errors.

 

[david.paton]

Thanks Steve, works perfectly. One more question, if no record is selected when the edit button is hit, it throws back an error. How do I change the code so that it just displays a msg box asking you to select a record?

 

[sneuberg]

Just check it for empty string or null like:

If Me.SearchResults & vbNullString = vbNullString Then
    MsgBox "Select a record"
    Exit Sub
End If

DoCmd.OpenForm "widow", acNormal, , "[WidowID] = Forms![FRM_SearchMulti]![SearchResults]"

 

[david.paton]

Thanks again for that Steve, I tried to work it out myself and I broke it and I wasn't sure what I did so thanks for giving me all the code to paste back in to make it work.

 

[david.paton]

If I wanted to learn how to code like this in Access, do you have any recommendations of where I could go?

 

[sneuberg]

I suggest starting with this video series.