Reference rst field from form

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Is there a way to reference "resZip" in the following code from a form .. eg

MyFile = ![Me.cboFieldName]

where the cboFieldName = rezZip

The recordset contains one field which groups a set a set of data, in this
case a zip code. But at times it may be City, County, Country.

----------------------------------------------------------------------

Set rst2 = CurrentDb.OpenRecordset("qryGroupField")

With rst2
Do Until .EOF
Myfile = ![resZip] **(i want to change this line to a
variable from a combobox)**

Export function which runs a query based on the value of resZip and exports
a file.

..MoveNext
Loop
 
Craig said:
Is there a way to reference "resZip" in the following code from a form ..
eg

MyFile = ![Me.cboFieldName]

where the cboFieldName = rezZip

The recordset contains one field which groups a set a set of data, in this
case a zip code. But at times it may be City, County, Country.

----------------------------------------------------------------------

Set rst2 = CurrentDb.OpenRecordset("qryGroupField")

With rst2
Do Until .EOF
Myfile = ![resZip] **(i want to change this line to a
variable from a combobox)**

Export function which runs a query based on the value of resZip and
exports a file.

.MoveNext
Loop
Click to expand...

So the name of the combo box is to come from the [resZip] field in the
recordset? If I've understood you, you could write that like this:

With rst2
' ...
MyFile = Me.Controls(![resZip])
' ...
End With

But I'm not sure if that's what you meant. If you meant it the other way
around, and instead the value of the combo box is supposed to identify which
field of the recordset you want to pull. In that case, you would write it
like this:

With rst2
' ...
MyFile = .Fields(Me.cboFieldName)
' ...
End With
 
Dirk Goldgar said:
Craig said:
Is there a way to reference "resZip" in the following code from a form ..
eg

MyFile = ![Me.cboFieldName]

where the cboFieldName = rezZip

The recordset contains one field which groups a set a set of data, in
this case a zip code. But at times it may be City, County, Country.

----------------------------------------------------------------------

Set rst2 = CurrentDb.OpenRecordset("qryGroupField")

With rst2
Do Until .EOF
Myfile = ![resZip] **(i want to change this line to a
variable from a combobox)**

Export function which runs a query based on the value of resZip and
exports a file.

.MoveNext
Loop
Click to expand...

So the name of the combo box is to come from the [resZip] field in the
recordset? If I've understood you, you could write that like this:

With rst2
' ...
MyFile = Me.Controls(![resZip])
' ...
End With

But I'm not sure if that's what you meant. If you meant it the other way
around, and instead the value of the combo box is supposed to identify
which field of the recordset you want to pull. In that case, you would
write it like this:

With rst2
' ...
MyFile = .Fields(Me.cboFieldName)
' ...
End With


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...


With rst2
' ...
MyFile = .Fields(Me.cboFieldName)
' ...
End With

This is what I was looking for. It now works perfectly. I tried a number
of variations but just could not get it to work. I kept on getting the
"Item not found in this collection" message

Many, Many Thanks

Craig
 
Craig said:
Is there a way to reference "resZip" in the following code from a form .. eg

MyFile = ![Me.cboFieldName]

where the cboFieldName = rezZip

The recordset contains one field which groups a set a set of data, in this
case a zip code. But at times it may be City, County, Country.

----------------------------------------------------------------------

Set rst2 = CurrentDb.OpenRecordset("qryGroupField")

With rst2
Do Until .EOF
Myfile = ![resZip] **(i want to change this line to a
variable from a combobox)**

Export function which runs a query based on the value of resZip and exports
a file.

.MoveNext
Loop
Click to expand...

Try using this kind of syntax:

Myfile = .Fields(Me.combobox)
 
Back
Top