define correct variable type

[Guest]

I am atttempting to use the same multiselect list box form in a couple places
in my application by using "openArgs" to determine which button open the form
and then redefining the list box row sources depending on the openArgs. One
of the features on this form is the ability to add user defined choices to
the listbox list using the following code. I have attempted to define the
name of the field the new list item will be placed using a string variable
but this does not seem to work. (see arrows)

Private Sub AddToList_Click()
On Error GoTo Err_AddToList_Click

Dim db As Database
Dim rstCathList As DAO.Recordset
Dim AddStr As String
Dim strField as String '<--define variable as string

If OpenArgs = "TestForm" Then
strField = "TestList" '<--using openArgs to determine which field to
update
End If

AddStr = [NewItem]
Set db = CurrentDb
Set rstCathList = db.OpenRecordset(strRecordset)
rstCathList.AddNew
rstCathList!strField = AddStr '<--rstCathList!strField is not recognized
rstCathList.Update

Me.Refresh

Exit_AddToList_Click:
Exit Sub

Err_AddToList_Click:
MsgBox Err.Description
Resume Exit_AddToList_Click

End Sub

 

[Steve C]

Hi Steve,

I believe you just need to change this line
rstCathList!strField = AddStr

to
rstCathList(strField) = AddStr

HTH
Steve C

 

[Allen Browne]

Try:
rstCathList(strField) = AddStr

That's short for:
rstCathList.Fields(strField) = AddStr

 

[Guest]

A memory variable and a field in a table are not the same thing. You are
Dimming a memory variable, but trying to use it as a recordset field. Also,
what is [NewItem]?
What do you mean by determining which button opens the form?

It is not clear why you are updating a record in a table to determine which
field to use as the rowsource for a list box.

I am not sure what you are doing because of the confusion, but if you are
trying to set the row source based on the OpenArgs, it would typically be
done like this. (Assume the value of OpenArgs is the name of the field you
want to use):

If IsNull(Me.OpenARgs) Then
MsgBox "No Row Source for MyListBox Selected"
Else
Me.MyListBox.RowSource = "SELECT " & Me.OpenArgs & " FROM SomeTable;"
End If