Loading a particular record into a form via a command button

[Guest]

I want to load a record into a form based on a user choosing a name from a
combo box. When the name is chosen the user then clicks a command button
that loads the record of that name into the form. The records are contained
in a table. I was thinking of using the DoCmd.GoToRecord method but have no
idea how to tell it which record to go to. I want to go to the record that
matches the name in the combo box (the name is in a field in the table). Any
suggestions?

 

[fredg]

I want to load a record into a form based on a user choosing a name from a
combo box. When the name is chosen the user then clicks a command button
that loads the record of that name into the form. The records are contained
in a table. I was thinking of using the DoCmd.GoToRecord method but have no
idea how to tell it which record to go to. I want to go to the record that
matches the name in the combo box (the name is in a field in the table). Any
suggestions?

If you add a new unbound combo box to the form, using the combo box
wizard, select the 3rd option on the first page of instructions (Find
a record ... etc.) and the wizard will take care of this for you. No
need for a separate command button.

 

[Marshall Barton]

I want to load a record into a form based on a user choosing a name from a
combo box. When the name is chosen the user then clicks a command button
that loads the record of that name into the form. The records are contained
in a table. I was thinking of using the DoCmd.GoToRecord method but have no
idea how to tell it which record to go to. I want to go to the record that
matches the name in the combo box (the name is in a field in the table).

Loading the matching recor(s?) is quite different than
loading all the records and then finding the matching
record. In general, you'll get much better performance by
only loading the required record.

This can be done by manipulating the form's RecordSource
property:

Dim strSQL As String
strSQL = "SELECT fld1, fld2, ... FROM table " _
& "WHERE [namefield] = """ & Me.combo & """"
Me.RecordSource = strSQL

OTOH, if you need to be able to browse through a set of
records and want to navigate to the record identified in the
combo box:

With Me.RecordsetClone
.FindFirst "[namefield] = """ & Me.combo & """"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

The above assumes that the namefield is a Text type field.
If it's a numeric field, then don't use the extra quotes:
. . . "[namefield] = " & Me.combo

 

[Guest]

I should have said..."When the name is chosen, the user clicks a command
button which opens a different form with the records of the user shown in the
combo box. These records are in a table. I understand the SQL statement,
but how do you assign the results to the various text boxes on the form?

I want to load a record into a form based on a user choosing a name from a
combo box. When the name is chosen the user then clicks a command button
that loads the record of that name into the form. The records are contained
in a table. I was thinking of using the DoCmd.GoToRecord method but have no
idea how to tell it which record to go to. I want to go to the record that
matches the name in the combo box (the name is in a field in the table).

Loading the matching recor(s?) is quite different than
loading all the records and then finding the matching
record. In general, you'll get much better performance by
only loading the required record.

This can be done by manipulating the form's RecordSource
property:

Dim strSQL As String
strSQL = "SELECT fld1, fld2, ... FROM table " _
& "WHERE [namefield] = """ & Me.combo & """"
Me.RecordSource = strSQL

OTOH, if you need to be able to browse through a set of
records and want to navigate to the record identified in the
combo box:

With Me.RecordsetClone
.FindFirst "[namefield] = """ & Me.combo & """"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

The above assumes that the namefield is a Text type field.
If it's a numeric field, then don't use the extra quotes:
. . . "[namefield] = " & Me.combo

 

[Marshall Barton]

Ok. For this question (as opposed to your original
question), I recommend that you use the OpenForm method's
WhereCondition argument. Still assuming that the keyfield is
a Text type field, the button's Click event procedure would
include code something like this:

Dim strWhere As String
strWhere = "[namefield] = """ & Me.combo & """"
DoCmd.OpenForm "nameofform", , , strWhere
--
Marsh
MVP [MS Access]

I should have said..."When the name is chosen, the user clicks a command
button which opens a different form with the records of the user shown in the
combo box. These records are in a table. I understand the SQL statement,
but how do you assign the results to the various text boxes on the form?

I want to load a record into a form based on a user choosing a name from a
combo box. When the name is chosen the user then clicks a command button
that loads the record of that name into the form. The records are contained
in a table. I was thinking of using the DoCmd.GoToRecord method but have no
idea how to tell it which record to go to. I want to go to the record that
matches the name in the combo box (the name is in a field in the table).

Loading the matching recor(s?) is quite different than
loading all the records and then finding the matching
record. In general, you'll get much better performance by
only loading the required record.

This can be done by manipulating the form's RecordSource
property:

Dim strSQL As String
strSQL = "SELECT fld1, fld2, ... FROM table " _
& "WHERE [namefield] = """ & Me.combo & """"
Me.RecordSource = strSQL

OTOH, if you need to be able to browse through a set of
records and want to navigate to the record identified in the
combo box:

With Me.RecordsetClone
.FindFirst "[namefield] = """ & Me.combo & """"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

The above assumes that the namefield is a Text type field.
If it's a numeric field, then don't use the extra quotes:
. . . "[namefield] = " & Me.combo

 

[Guest]

Sorry, but this didn't help. Perhaps I just don't understand Access well
enough.. I want to do this... 1. Pick a name from a combo box 2. Click a
command button - the command button will use the name from the combo box to
load the record which has the same name in one of it's fields (I made it a
key field) in a particular table into a particular form. I'm sorry, but I'm
just not clear as to how to code the button to grab all the other fields from
this record as well as the one with the same name as the combo box and put
them into the form

Ok. For this question (as opposed to your original
question), I recommend that you use the OpenForm method's
WhereCondition argument. Still assuming that the keyfield is
a Text type field, the button's Click event procedure would
include code something like this:

Dim strWhere As String
strWhere = "[namefield] = """ & Me.combo & """"
DoCmd.OpenForm "nameofform", , , strWhere
--
Marsh
MVP [MS Access]

I should have said..."When the name is chosen, the user clicks a command
button which opens a different form with the records of the user shown in the
combo box. These records are in a table. I understand the SQL statement,
but how do you assign the results to the various text boxes on the form?

ndunwoodie wrote:

I want to load a record into a form based on a user choosing a name from a
combo box. When the name is chosen the user then clicks a command button
that loads the record of that name into the form. The records are contained
in a table. I was thinking of using the DoCmd.GoToRecord method but have no
idea how to tell it which record to go to. I want to go to the record that
matches the name in the combo box (the name is in a field in the table).


Loading the matching recor(s?) is quite different than
loading all the records and then finding the matching
record. In general, you'll get much better performance by
only loading the required record.

This can be done by manipulating the form's RecordSource
property:

Dim strSQL As String
strSQL = "SELECT fld1, fld2, ... FROM table " _
& "WHERE [namefield] = """ & Me.combo & """"
Me.RecordSource = strSQL

OTOH, if you need to be able to browse through a set of
records and want to navigate to the record identified in the
combo box:

With Me.RecordsetClone
.FindFirst "[namefield] = """ & Me.combo & """"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

The above assumes that the namefield is a Text type field.
If it's a numeric field, then don't use the extra quotes:
. . . "[namefield] = " & Me.combo

 

[Marshall Barton]

Do you have any code for the button? The button wizard will
generate most of it for you. Once you have that, modify it
to include what I posted. If you can't figure out to make
the changes, at least post a Copy/Paste of the code that you
do have.

You have created to other form, right? The form has a table
or a query as its record source, right? Basically, if you
open the form from that database window, it should show you
all the records in the tabe/query. When you open it using
the button's Click event code, it should filter the records
to the one with the selected name.

If that's still not enough to get you going, it would help
if you provided more details about the form, the combo box
and the table/query.
--
Marsh
MVP [MS Access]

Sorry, but this didn't help. Perhaps I just don't understand Access well
enough.. I want to do this... 1. Pick a name from a combo box 2. Click a
command button - the command button will use the name from the combo box to
load the record which has the same name in one of it's fields (I made it a
key field) in a particular table into a particular form. I'm sorry, but I'm
just not clear as to how to code the button to grab all the other fields from
this record as well as the one with the same name as the combo box and put
them into the form

Ok. For this question (as opposed to your original
question), I recommend that you use the OpenForm method's
WhereCondition argument. Still assuming that the keyfield is
a Text type field, the button's Click event procedure would
include code something like this:

Dim strWhere As String
strWhere = "[namefield] = """ & Me.combo & """"
DoCmd.OpenForm "nameofform", , , strWhere

 

[Guest]

Here's what I have...

Private Sub cmdRevGoal_Click()
On Error GoTo Err_cmdRevGoal_Click

Dim strDocName As String
Dim stLinkCriteria As String
Dim strSQL As String
Dim strWhere As String
strDocName = "frmRBES1"
'strWhere = "txtStaffMember = """ & Me.cboName & """"

'strSQL = "SELECT txtGoal,
txtRationale,txtSchoolRenewArea,txtBaseData,txtIndOfSuccess,txtMeasMeth,txtImpPlan,txtTimeLine FROM RBES1 " _
'& "WHERE [txtStaffMember] = """ & Me.cboName & """"
DoCmd.OpenForm "frmRBES1", , , strWhere
'DoCmd.OpenTable "RBES1"
'Me.SetFocus
'Forms!frmRBES1.RecordSource = strSQL
Forms!frmRBES1.txtRB1_StaffMem = cboName
'Forms!frmRBES1.[txtRB1_Goal] = RBES1.txtGoal

Forms!frmRBES1.txtRationale = ?
Forms!frmRBES1.txtSchoolRenewArea = ?
Forms!frmRBES1.txtBaseData = ?
Forms!frmRBES1.txtIndOfSuccess = ?
Forms!frmRBES1.txtMeasMeth = ?
Forms!frmRBES1.txtImpPlan = ?
Forms!frmRBES1.txtTimeLine = ?

Exit_cmdRevGoal_Click:
Exit Sub

Err_cmdRevGoal_Click:
MsgBox Err.Description
Resume Exit_cmdRevGoal_Click

End Sub
The lines that are commented out are attempts that I made with your earlier
suggestions. The only thing that does work is the line that opens the form
and the line that puts the name from the combo box into the respective text
box on the form (Forms!frmRBES1.txtRB1_StaffMem = cboName). I want to take
the remaining fields from the record that has the particular
txtRBES1_StaffMem and put them in the other text boxes on the form
(Forms!frmRBES1.txtRationale ,...etc.). The records are contained in a table
named RBES1. I had tried that with the lines that end with a question mark.
Am I way out in outer space with my thinking on this?

Do you have any code for the button? The button wizard will
generate most of it for you. Once you have that, modify it
to include what I posted. If you can't figure out to make
the changes, at least post a Copy/Paste of the code that you
do have.

You have created to other form, right? The form has a table
or a query as its record source, right? Basically, if you
open the form from that database window, it should show you
all the records in the tabe/query. When you open it using
the button's Click event code, it should filter the records
to the one with the selected name.

If that's still not enough to get you going, it would help
if you provided more details about the form, the combo box
and the table/query.
--
Marsh
MVP [MS Access]

Sorry, but this didn't help. Perhaps I just don't understand Access well
enough.. I want to do this... 1. Pick a name from a combo box 2. Click a
command button - the command button will use the name from the combo box to
load the record which has the same name in one of it's fields (I made it a
key field) in a particular table into a particular form. I'm sorry, but I'm
just not clear as to how to code the button to grab all the other fields from
this record as well as the one with the same name as the combo box and put
them into the form

Ok. For this question (as opposed to your original
question), I recommend that you use the OpenForm method's
WhereCondition argument. Still assuming that the keyfield is
a Text type field, the button's Click event procedure would
include code something like this:

Dim strWhere As String
strWhere = "[namefield] = """ & Me.combo & """"
DoCmd.OpenForm "nameofform", , , strWhere

 

[Guest]

Marshall,
Ignore my last post. I solved the problem by having the button run a query
that was made the record source for the form to be opened. Using the LIKE
command, I was able to tell the query to use the contents of the combo box.
Thanks for your help.

Do you have any code for the button? The button wizard will
generate most of it for you. Once you have that, modify it
to include what I posted. If you can't figure out to make
the changes, at least post a Copy/Paste of the code that you
do have.

You have created to other form, right? The form has a table
or a query as its record source, right? Basically, if you
open the form from that database window, it should show you
all the records in the tabe/query. When you open it using
the button's Click event code, it should filter the records
to the one with the selected name.

If that's still not enough to get you going, it would help
if you provided more details about the form, the combo box
and the table/query.
--
Marsh
MVP [MS Access]

Sorry, but this didn't help. Perhaps I just don't understand Access well
enough.. I want to do this... 1. Pick a name from a combo box 2. Click a
command button - the command button will use the name from the combo box to
load the record which has the same name in one of it's fields (I made it a
key field) in a particular table into a particular form. I'm sorry, but I'm
just not clear as to how to code the button to grab all the other fields from
this record as well as the one with the same name as the combo box and put
them into the form

Ok. For this question (as opposed to your original
question), I recommend that you use the OpenForm method's
WhereCondition argument. Still assuming that the keyfield is
a Text type field, the button's Click event procedure would
include code something like this:

Dim strWhere As String
strWhere = "[namefield] = """ & Me.combo & """"
DoCmd.OpenForm "nameofform", , , strWhere

 

[Marshall Barton]

Here's what I have...

Private Sub cmdRevGoal_Click()
On Error GoTo Err_cmdRevGoal_Click

Dim strDocName As String
Dim stLinkCriteria As String
Dim strSQL As String
Dim strWhere As String
strDocName = "frmRBES1"
'strWhere = "txtStaffMember = """ & Me.cboName & """"

'strSQL = "SELECT txtGoal,
txtRationale,txtSchoolRenewArea,txtBaseData,txtIndOfSuccess,txtMeasMeth,txtImpPlan,txtTimeLine FROM RBES1 " _
'& "WHERE [txtStaffMember] = """ & Me.cboName & """"
DoCmd.OpenForm "frmRBES1", , , strWhere
'DoCmd.OpenTable "RBES1"
'Me.SetFocus
'Forms!frmRBES1.RecordSource = strSQL
Forms!frmRBES1.txtRB1_StaffMem = cboName
'Forms!frmRBES1.[txtRB1_Goal] = RBES1.txtGoal

Forms!frmRBES1.txtRationale = ?
Forms!frmRBES1.txtSchoolRenewArea = ?
Forms!frmRBES1.txtBaseData = ?
Forms!frmRBES1.txtIndOfSuccess = ?
Forms!frmRBES1.txtMeasMeth = ?
Forms!frmRBES1.txtImpPlan = ?
Forms!frmRBES1.txtTimeLine = ?

Exit_cmdRevGoal_Click:
Exit Sub

Err_cmdRevGoal_Click:
MsgBox Err.Description
Resume Exit_cmdRevGoal_Click

End Sub
The lines that are commented out are attempts that I made with your earlier
suggestions. The only thing that does work is the line that opens the form
and the line that puts the name from the combo box into the respective text
box on the form (Forms!frmRBES1.txtRB1_StaffMem = cboName). I want to take
the remaining fields from the record that has the particular
txtRBES1_StaffMem and put them in the other text boxes on the form
(Forms!frmRBES1.txtRationale ,...etc.). The records are contained in a table
named RBES1. I had tried that with the lines that end with a question mark.
Am I way out in outer space with my thinking on this?

I can't tell how close you are to achieving orbit, but you
have definitely left the launch pad ;-)

Your code attempts are mixing two different approaches to
this problem, but the record source technique would require
different, more complex code. What I was suggesting is:

Dim strDocName As String
Dim strWhere As String
strDocName = "frmRBES1"
strWhere = "txtStaffMember = """ & Me.cboName & """"
DoCmd.OpenForm "frmRBES1", , , strWhere

If the combo box's bound column contains a name (not the
more common key value of a names table record), that should
open the form to the record(s?) that match the name. If it
doesn't, then I still need to know about the fields in the
combo box's row source query, the column count and the bound
column.

I do not understand the other stuff. It looks like you may
be trying to copy the data from one form to another form??
That would generally be a No-No and if really required, it
would require a different approach. For now, at least,
let's leave that until you get the form to open properly.