"set" control value and pass to module

[rglman]

Next challenge;
I used to do this with a macro, but now I want to do it in VB.

I had a macro that would open a modal form with an unbound listbox control, the user selects a value from the drop-down list, and then clicks an "OK" button. The button would fire another macro that would set the value so that a query def subsequently opened by the original macro would have the value passed into it. The "OK' button macro was quite simple:

SetValue ( Item [Visible] )

Close ( Object Type Form
Object Name MyFormName
Save Yes )​

I tried to let Access convert it to a module, but the resulting module has errors and will not compile.
I get a syntax error on the ".visible =" line. Does anyone know what I need to do to fix this?

Function mac_sample()
On Error GoTo mac_sample_Err

With CodeContextObject
.Visible =
DoCmd.Close acForm, "frm_myFormName"
End With


mac_sample_Exit:
Exit Function

mac_sample_Err:
MsgBox Error$
Resume mac_sample_Exit

End Function​

Thanks in advance!

 

[Ian Mac]

See if an answer I have just provided fits your bill:

http://www.access-programmers.co.uk/forums/showthread.php?t=104067

This is a method I use to pass a String variable to a function trapping null values.
You can use the:

GetCOMBOVal()

in my example as the criteria for your QueryDef.

 

[Banaticus]

I get a syntax error on the ".visible =" line. Does anyone know what I need to do to fix this?

Yes, you need to set the Visible property to something, either True or False.

One of the two:
.Visible = True
.Visible = False

I'm not sure what it is that you're trying to do. What populates the list box if it's completely unbound? What do you have its RowSource property set to? How do you choose which column (if you have more than one) passes the value? You don't need a button that someone clicks after selecting a name, you can have the act of selecting a name change the form.

For instance, if your list box is names rglmanListBox, and if the primary field in the table that the form is based off is SocSec, then you could:

Private Sub rglmanListBox_AfterUpdate()
    Dim rs As Object
    Set rs = Me.RecordsetClone
    ' Find the record that matches the control
    rs.FindFirst "[SocSec] = '" & Me!rglmanListBox & "'"
    Me.Bookmark = rs.Bookmark
End Sub