Open Modal form with arguments and get return value

[dsajones]

Hi everyone,

I'm new to the world of Access and VBA. Have an excellent book, use VBA help and have scoured the web for a solution to my problems, but no joy so far.

What I want to do is pretty simple. It's just a look-up and selection of an address from one form to another. I have 2 seperate problems. The first is that opening the second form as modal with a selection of addresses displayed based on the post code entered just doesn't work. If the filtered selection works then the form isn't modal. If I get it to work as a modal form then the filter doesn't work. I've tried every possible permutation of ,,, etc. in the parameters of the DoCmd.OpenForm command.

The second problem is getting the selected address back to the first form.

Form A - user enters a post code then presses a button. When they press the button it should open Form B as a multi-record form with a list of addresses which match the post code entered.

Here's the code I currently have on the button which works to open the form as modal but doesn't apply the selection of records matching the post code:

Private Sub cmd_LookupPostCode_Click()
On Error GoTo Err_cmd_LookupPostCode_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_SelectStockAddress"

stLinkCriteria = "[PostCode]=" & "'" & Me![PostCodeLookup] & "'"
DoCmd.OpenForm stDocName, , , , , acDialog, stLinkCriteria

MsgBox "Did it wait?"
' Yes it did wait so the modal worked but the stLinkCriteria was ignored

Exit_cmd_LookupPostCode_Click:
Exit Sub
Err_cmd_LookupPostCode_Click:
MsgBox Err.Description
Resume Exit_cmd_LookupPostCode_Click

End Sub

If I use DoCmd.OpenForm stDocName, , , stLinkCriteria then the form opens as intended with the data I want but obviously not modal.


Form B -Designed as a multi-record form so the user can see all the addresses that match the post code entered. In the design there is a button against each record so that the correct address can be selected. The code then combines all the address fields together, gets rid of blank lines and inserts CRLF so that the address can be returned in a single field to populate the address field on form A. The final action of the code on the button is to close Form B and return control to Form A.

Private Sub Select_Address_Click()
On Error GoTo Err_Select_Address_Click
Dim msg As String
If Address1 <> "" Then
msg = Address1
End If

If Address2 <> "" Then
msg = msg + vbCrLf + Address2
End If

If Address3 <> "" Then
msg = msg + vbCrLf + Address3
End If

If Address4 <> "" Then
msg = msg + vbCrLf + Address4
End If

If PostCode <> "" Then
msg = msg + vbCrLf + PostCode
End If

MsgBox (msg)
getReturnedString = msg
DoCmd.Close


Exit_Select_Address_Click:
Exit Sub
Err_Select_Address_Click:
MsgBox Err.Description
Resume Exit_Select_Address_Click

End Sub


Anyone any ideas what I'm doing wrong in the first problem where I can't get the form to open modally and with the correct data displayed. And what do I need to do to get the data in the getReturnedString variable returned back to Form A and in to a field on Form A called Address.

Thanks in advance for any help offered.

Cheers
David

 

[pbaldy]

Have you tried with the two items in their appropriate positions?

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

 

[dsajones]

Hi Paul,

Thanks for speedy reply. Yes, that's worked! As I said, I'm new to VBA and totally reliant on the help system and inline completion etc. Both of these put the acDialog (WindowMode) before the openArgs

docmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

Looks like I'll need to play with different combinations until I hit the one that works!!

Anyway, thanks again for your help. Now to concentrate on getting my return value back.

Cheers
David

 

[pbaldy]

You're not using OpenArgs, you're using the WhereCondition. OpenArgs would have no direct effect on the form being opened, but would be a value available to it. You might find this sample helpful on getting the value back:

http://www.baldyweb.com/WrappedForm.htm

 

[dsajones]

Hi Paul. OK, I see what I did wrong now. Thanks.

Have managed to get the value returned from form B OK using the public variable getReturnedString. It wasn't working earlier as I was declaring it in the wrong place. So everything is working as planned now.

Thanks again for all your help.

David

 

[pbaldy]

Happy to help David, and welcome to the site by the way.

 

[smig]

I use a public variable to store the popUp form button clicked