Retrieving data from user input.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that has several fields, one of which is called Pro_ID. I
would like to use whatever number is entered in that field to run a query
that fills in the rest of the fields with information associated with that
Pro_ID. Any ideas as to how I can achieve this? Thanks.
 
Yepp said:
I have a form that has several fields, one of which is called Pro_ID. I
would like to use whatever number is entered in that field to run a query
that fills in the rest of the fields with information associated with that
Pro_ID. Any ideas as to how I can achieve this? Thanks.
Click to expand...

In the AfterUpdate event of the Pro_ID control, construct an SQL statement
to retrieve the values you want and place them in the appropriate controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same thing,
although using multiple DLookUps usually contributes a lot more overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
 
The typical way to do this is to use an umbound combo box with a row source
that provides a list of all the Pro_ID's in your form's recordset. Then you
use the After Update event of the combo to make the record for the selected
Pro_ID the current record.

Private Sub cboProID_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Pro_ID] = " & Me.cboProID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
 
Carl,

I tried your way (see below) but something is not working right. I keep
getting a compile error on the strSQL line. Am I doing something wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
Carl Rapson said:
Yepp said:
I have a form that has several fields, one of which is called Pro_ID. I
would like to use whatever number is entered in that field to run a query
that fills in the rest of the fields with information associated with that
Pro_ID. Any ideas as to how I can achieve this? Thanks.
Click to expand...

In the AfterUpdate event of the Pro_ID control, construct an SQL statement
to retrieve the values you want and place them in the appropriate controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same thing,
although using multiple DLookUps usually contributes a lot more overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
 
Quick question: where in your code does it say what to update? Does the code
just look for a number?

Klatuu said:
The typical way to do this is to use an umbound combo box with a row source
that provides a list of all the Pro_ID's in your form's recordset. Then you
use the After Update event of the combo to make the record for the selected
Pro_ID the current record.

Private Sub cboProID_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Pro_ID] = " & Me.cboProID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
--
Dave Hargis, Microsoft Access MVP


Yepp said:
I have a form that has several fields, one of which is called Pro_ID. I
would like to use whatever number is entered in that field to run a query
that fills in the rest of the fields with information associated with that
Pro_ID. Any ideas as to how I can achieve this? Thanks.
Click to expand...
Click to expand...
 
Is the control (not the field) really named 'txtPro_ID'? If not, use your
own control name there.

Carl Rapson

Yepp said:
Carl,

I tried your way (see below) but something is not working right. I keep
getting a compile error on the strSQL line. Am I doing something wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
Carl Rapson said:
Yepp said:
I have a form that has several fields, one of which is called Pro_ID. I
would like to use whatever number is entered in that field to run a
query
that fills in the rest of the fields with information associated with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.
Click to expand...

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same
thing,
although using multiple DLookUps usually contributes a lot more overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
 
Carl,

I used my control name instead of the 'txtPro_ID' but still nothing happens.
There are no errors when I run the code...just none of the info fills in.

Carl Rapson said:
Is the control (not the field) really named 'txtPro_ID'? If not, use your
own control name there.

Carl Rapson

Yepp said:
Carl,

I tried your way (see below) but something is not working right. I keep
getting a compile error on the strSQL line. Am I doing something wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
Carl Rapson said:
I have a form that has several fields, one of which is called Pro_ID. I
would like to use whatever number is entered in that field to run a
query
that fills in the rest of the fields with information associated with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same
thing,
although using multiple DLookUps usually contributes a lot more overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Put a breakpoint in your code and step through each line, checking the
values in the Immediate window. Is the AfterUpdate event actually firing (if
not, you'll never reach your breakpoint)? What does your SQL string look
like after you build it? Is the recordset actually being populated? Are
there values in the recordset fields? One thing I do frequently is print the
SQL string in the Immediate window, then copy the SQL statement and paste it
into an empty Query window (in SQL view). If the query doesn't run, I can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in the table)
text or numeric? If it's text, even if you're storing a number you'll need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity, here's the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Yepp said:
Carl,

I used my control name instead of the 'txtPro_ID' but still nothing
happens.
There are no errors when I run the code...just none of the info fills in.

Carl Rapson said:
Is the control (not the field) really named 'txtPro_ID'? If not, use your
own control name there.

Carl Rapson

Yepp said:
Carl,

I tried your way (see below) but something is not working right. I
keep
getting a compile error on the strSQL line. Am I doing something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is called Pro_ID.
I
would like to use whatever number is entered in that field to run a
query
that fills in the rest of the fields with information associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same
thing,
although using multiple DLookUps usually contributes a lot more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Carl,

I put breaks in the code and keep getting errors on the lines in the if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text box's
control name> = rs.Fields![the field name of the text box].

Carl Rapson said:
Put a breakpoint in your code and step through each line, checking the
values in the Immediate window. Is the AfterUpdate event actually firing (if
not, you'll never reach your breakpoint)? What does your SQL string look
like after you build it? Is the recordset actually being populated? Are
there values in the recordset fields? One thing I do frequently is print the
SQL string in the Immediate window, then copy the SQL statement and paste it
into an empty Query window (in SQL view). If the query doesn't run, I can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in the table)
text or numeric? If it's text, even if you're storing a number you'll need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity, here's the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Yepp said:
Carl,

I used my control name instead of the 'txtPro_ID' but still nothing
happens.
There are no errors when I run the code...just none of the info fills in.

Carl Rapson said:
Is the control (not the field) really named 'txtPro_ID'? If not, use your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working right. I
keep
getting a compile error on the strSQL line. Am I doing something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is called Pro_ID.
I
would like to use whatever number is entered in that field to run a
query
that fills in the rest of the fields with information associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same
thing,
although using multiple DLookUps usually contributes a lot more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
What errors are you getting? Are you certain you're using the correct names
for the controls and the table fields? Did you try running the query
standalone? What does your SQL string look like in the Immediate window?

What are the control names (the Name property in the Properties window for
each control) and the field names (from the table)?

Carl Rapson

Yepp said:
Carl,

I put breaks in the code and keep getting errors on the lines in the if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text box's
control name> = rs.Fields![the field name of the text box].

Carl Rapson said:
Put a breakpoint in your code and step through each line, checking the
values in the Immediate window. Is the AfterUpdate event actually firing
(if
not, you'll never reach your breakpoint)? What does your SQL string look
like after you build it? Is the recordset actually being populated? Are
there values in the recordset fields? One thing I do frequently is print
the
SQL string in the Immediate window, then copy the SQL statement and paste
it
into an empty Query window (in SQL view). If the query doesn't run, I can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in the
table)
text or numeric? If it's text, even if you're storing a number you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity, here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Yepp said:
Carl,

I used my control name instead of the 'txtPro_ID' but still nothing
happens.
There are no errors when I run the code...just none of the info fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If not, use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working right. I
keep
getting a compile error on the strSQL line. Am I doing something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is called
Pro_ID.
I
would like to use whatever number is entered in that field to run
a
query
that fills in the rest of the fields with information associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same
thing,
although using multiple DLookUps usually contributes a lot more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Hi -

I don't know what version of Access you have, but in A2000 your code won't
work because of the if rs.recordcount > 0 line. The recordcount for a
recordset is not accurate until all the records in the recordset have been
accessed. Since in your IF statement you have not accessed any records yet,
the Recordcount property is still 0.

To check if the retrieval returned any records, you can use

if not rs.BOF then....

I suspect this might have been the problem when you stated that the code
compiled and ran without errors, but no fields were filled in.

HTH

John




Quick question: where in your code does it say what to update? Does the code
just look for a number?
The typical way to do this is to use an umbound combo box with a row source
that provides a list of all the Pro_ID's in your form's recordset. Then you
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
J_Goddard via AccessMonster.com said:
I don't know what version of Access you have, but in A2000 your code won't
work because of the if rs.recordcount > 0 line. The recordcount for a
recordset is not accurate until all the records in the recordset have been
accessed. Since in your IF statement you have not accessed any records yet,
the Recordcount property is still 0.

To check if the retrieval returned any records, you can use

if not rs.BOF then....

I suspect this might have been the problem when you stated that the code
compiled and ran without errors, but no fields were filled in.
Click to expand...


Saying the RecordCount is not accurate is not the same as
saying it is totally inaccurate. While it may not give the
total number of records in the recordset, it is guaranteed
to return a value greater than 0 if the recordset returns at
least one record. The key to thinking about this issue is
that "the number of records accessed so far" refers to
Access accessing the records, which is not necessarily the
same as the number of records the VBA code has accessed.

Checking RecordCount>0 on a newly opened recordset is just
as valid as checking BOF And EOF. This is true because if
the recordset returns any records, then Access has retrieved
at least one record. I.e. If neither BOF nor EOF is True,
then there must be a current record (which is guaranteed to
be the first record in a newly opened recordset).
 
I am getting a compile error (method or data member not found) and also the
"item not found in this collection" error. I ran the query by itself to test
it and it worked fine. I checked the names to make sure they were
correct...that is what is puzzling.

The names of the controls are Pro Number, Pro Last Name, and Pro First Name.
The field names from the table are Pro_Number, Pro_Last_Name, and
Pro_First_Name.

Carl Rapson said:
What errors are you getting? Are you certain you're using the correct names
for the controls and the table fields? Did you try running the query
standalone? What does your SQL string look like in the Immediate window?
Click to expand...
What are the control names (the Name property in the Properties window for
each control) and the field names (from the table)?

Carl Rapson

Yepp said:
Carl,

I put breaks in the code and keep getting errors on the lines in the if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text box's
control name> = rs.Fields![the field name of the text box].

Carl Rapson said:
Put a breakpoint in your code and step through each line, checking the
values in the Immediate window. Is the AfterUpdate event actually firing
(if
not, you'll never reach your breakpoint)? What does your SQL string look
like after you build it? Is the recordset actually being populated? Are
there values in the recordset fields? One thing I do frequently is print
the
SQL string in the Immediate window, then copy the SQL statement and paste
it
into an empty Query window (in SQL view). If the query doesn't run, I can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in the
table)
text or numeric? If it's text, even if you're storing a number you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity, here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Carl,

I used my control name instead of the 'txtPro_ID' but still nothing
happens.
There are no errors when I run the code...just none of the info fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If not, use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working right. I
keep
getting a compile error on the strSQL line. Am I doing something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is called
Pro_ID.
I
would like to use whatever number is entered in that field to run
a
query
that fills in the rest of the fields with information associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the same
thing,
although using multiple DLookUps usually contributes a lot more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Thank you for the clarification - I had mis-read the help item

J.


Marshall said:
I don't know what version of Access you have, but in A2000 your code won't
work because of the if rs.recordcount > 0 line. The recordcount for a
Click to expand...
[quoted text clipped - 8 lines]
I suspect this might have been the problem when you stated that the code
compiled and ran without errors, but no fields were filled in.
Click to expand...

Saying the RecordCount is not accurate is not the same as
saying it is totally inaccurate. While it may not give the
total number of records in the recordset, it is guaranteed
to return a value greater than 0 if the recordset returns at
least one record. The key to thinking about this issue is
that "the number of records accessed so far" refers to
Access accessing the records, which is not necessarily the
same as the number of records the VBA code has accessed.

Checking RecordCount>0 on a newly opened recordset is just
as valid as checking BOF And EOF. This is true because if
the recordset returns any records, then Access has retrieved
at least one record. I.e. If neither BOF nor EOF is True,
then there must be a current record (which is guaranteed to
be the first record in a newly opened recordset).
Click to expand...
 
Just as an experiment, try changing the names of the controls on the form to
something like "txtPro_Last_Name", to differentiate them from the table
field names. Then, use the new control names:

Me.txtPro_Last_Name = rs.Fields![Pro_Last_Name]

Does the error still occur?

Carl Rapson

Yepp said:
I am getting a compile error (method or data member not found) and also the
"item not found in this collection" error. I ran the query by itself to
test
it and it worked fine. I checked the names to make sure they were
correct...that is what is puzzling.

The names of the controls are Pro Number, Pro Last Name, and Pro First
Name.
The field names from the table are Pro_Number, Pro_Last_Name, and
Pro_First_Name.

Carl Rapson said:
What errors are you getting? Are you certain you're using the correct
names
for the controls and the table fields? Did you try running the query
standalone? What does your SQL string look like in the Immediate window?
Click to expand...
What are the control names (the Name property in the Properties window
for
each control) and the field names (from the table)?

Carl Rapson

Yepp said:
Carl,

I put breaks in the code and keep getting errors on the lines in the if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text
box's
control name> = rs.Fields![the field name of the text box].

:

Put a breakpoint in your code and step through each line, checking the
values in the Immediate window. Is the AfterUpdate event actually
firing
(if
not, you'll never reach your breakpoint)? What does your SQL string
look
like after you build it? Is the recordset actually being populated?
Are
there values in the recordset fields? One thing I do frequently is
print
the
SQL string in the Immediate window, then copy the SQL statement and
paste
it
into an empty Query window (in SQL view). If the query doesn't run, I
can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in the
table)
text or numeric? If it's text, even if you're storing a number you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity, here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Carl,

I used my control name instead of the 'txtPro_ID' but still nothing
happens.
There are no errors when I run the code...just none of the info
fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If not,
use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working right.
I
keep
getting a compile error on the strSQL line. Am I doing something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM
[prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is called
Pro_ID.
I
would like to use whatever number is entered in that field to
run
a
query
that fills in the rest of the fields with information
associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the
appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the
same
thing,
although using multiple DLookUps usually contributes a lot more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Also, J_Goddard brought up a good point about recordsets. Even though the
query is working, maybe the recordset itself isn't populated. Did you check
the recordset in Debug mode? Another thing you could try is to force the
recordset to load completely with:

rs.MoveLast
rs.MoveFirst

right after you open the recordset. Generally, I've found that if only a
single record is being returned this wasn't needed, but it might make a
difference.

Carl Rapson

Yepp said:
I am getting a compile error (method or data member not found) and also the
"item not found in this collection" error. I ran the query by itself to
test
it and it worked fine. I checked the names to make sure they were
correct...that is what is puzzling.

The names of the controls are Pro Number, Pro Last Name, and Pro First
Name.
The field names from the table are Pro_Number, Pro_Last_Name, and
Pro_First_Name.

Carl Rapson said:
What errors are you getting? Are you certain you're using the correct
names
for the controls and the table fields? Did you try running the query
standalone? What does your SQL string look like in the Immediate window?
Click to expand...
What are the control names (the Name property in the Properties window
for
each control) and the field names (from the table)?

Carl Rapson

Yepp said:
Carl,

I put breaks in the code and keep getting errors on the lines in the if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text
box's
control name> = rs.Fields![the field name of the text box].

:

Put a breakpoint in your code and step through each line, checking the
values in the Immediate window. Is the AfterUpdate event actually
firing
(if
not, you'll never reach your breakpoint)? What does your SQL string
look
like after you build it? Is the recordset actually being populated?
Are
there values in the recordset fields? One thing I do frequently is
print
the
SQL string in the Immediate window, then copy the SQL statement and
paste
it
into an empty Query window (in SQL view). If the query doesn't run, I
can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in the
table)
text or numeric? If it's text, even if you're storing a number you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity, here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Carl,

I used my control name instead of the 'txtPro_ID' but still nothing
happens.
There are no errors when I run the code...just none of the info
fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If not,
use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working right.
I
keep
getting a compile error on the strSQL line. Am I doing something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM
[prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is called
Pro_ID.
I
would like to use whatever number is entered in that field to
run
a
query
that fills in the rest of the fields with information
associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the
appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the
same
thing,
although using multiple DLookUps usually contributes a lot more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Yes, I still get the error after making the changes. It just highlights the
line in yellow. The names are definitely correct.

Carl Rapson said:
Just as an experiment, try changing the names of the controls on the form to
something like "txtPro_Last_Name", to differentiate them from the table
field names. Then, use the new control names:

Me.txtPro_Last_Name = rs.Fields![Pro_Last_Name]

Does the error still occur?

Carl Rapson

Yepp said:
I am getting a compile error (method or data member not found) and also the
"item not found in this collection" error. I ran the query by itself to
test
it and it worked fine. I checked the names to make sure they were
correct...that is what is puzzling.

The names of the controls are Pro Number, Pro Last Name, and Pro First
Name.
The field names from the table are Pro_Number, Pro_Last_Name, and
Pro_First_Name.

Carl Rapson said:
What errors are you getting? Are you certain you're using the correct
names
for the controls and the table fields? Did you try running the query
standalone? What does your SQL string look like in the Immediate window?
Click to expand...
What are the control names (the Name property in the Properties window
for
each control) and the field names (from the table)?

Carl Rapson

Carl,

I put breaks in the code and keep getting errors on the lines in the if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text
box's
control name> = rs.Fields![the field name of the text box].

:

Put a breakpoint in your code and step through each line, checking the
values in the Immediate window. Is the AfterUpdate event actually
firing
(if
not, you'll never reach your breakpoint)? What does your SQL string
look
like after you build it? Is the recordset actually being populated?
Are
there values in the recordset fields? One thing I do frequently is
print
the
SQL string in the Immediate window, then copy the SQL statement and
paste
it
into an empty Query window (in SQL view). If the query doesn't run, I
can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in the
table)
text or numeric? If it's text, even if you're storing a number you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity, here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Carl,

I used my control name instead of the 'txtPro_ID' but still nothing
happens.
There are no errors when I run the code...just none of the info
fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If not,
use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working right.
I
keep
getting a compile error on the strSQL line. Am I doing something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM
[prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is called
Pro_ID.
I
would like to use whatever number is entered in that field to
run
a
query
that fills in the rest of the fields with information
associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an SQL
statement
to retrieve the values you want and place them in the
appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do the
same
thing,
although using multiple DLookUps usually contributes a lot more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" & Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" & Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Looking back at your original post, I think I can see a problem. Whatever
name you use in your SELECT statement, that's the name you need to use in
your assignment statement. So, for example, if your SQL statement is:

strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE
...."

then the names you need to use in your assignment statements are LAST_NAME
and FIRST_NAME:

Me.txtPro_Last_Name = rs.Fields![LAST_NAME]
Me.txtPro_First_Name = rs.Fields![FIRST_NAME]

The field names must match exactly. So once again, what are the names of the
fields in the table? Open the table in design view and look at the names.
Those are the names that you must have in your SELECT statement and that you
must use in your assignment statement.

Carl Rapson

Yepp said:
Yes, I still get the error after making the changes. It just highlights
the
line in yellow. The names are definitely correct.

Carl Rapson said:
Just as an experiment, try changing the names of the controls on the form
to
something like "txtPro_Last_Name", to differentiate them from the table
field names. Then, use the new control names:

Me.txtPro_Last_Name = rs.Fields![Pro_Last_Name]

Does the error still occur?

Carl Rapson

Yepp said:
I am getting a compile error (method or data member not found) and also
the
"item not found in this collection" error. I ran the query by itself
to
test
it and it worked fine. I checked the names to make sure they were
correct...that is what is puzzling.

The names of the controls are Pro Number, Pro Last Name, and Pro First
Name.
The field names from the table are Pro_Number, Pro_Last_Name, and
Pro_First_Name.

:

What errors are you getting? Are you certain you're using the correct
names
for the controls and the table fields? Did you try running the query
standalone? What does your SQL string look like in the Immediate
window?

What are the control names (the Name property in the Properties window
for
each control) and the field names (from the table)?

Carl Rapson

Carl,

I put breaks in the code and keep getting errors on the lines in the
if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text
box's
control name> = rs.Fields![the field name of the text box].

:

Put a breakpoint in your code and step through each line, checking
the
values in the Immediate window. Is the AfterUpdate event actually
firing
(if
not, you'll never reach your breakpoint)? What does your SQL string
look
like after you build it? Is the recordset actually being populated?
Are
there values in the recordset fields? One thing I do frequently is
print
the
SQL string in the Immediate window, then copy the SQL statement and
paste
it
into an empty Query window (in SQL view). If the query doesn't run,
I
can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in
the
table)
text or numeric? If it's text, even if you're storing a number
you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity,
here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Carl,

I used my control name instead of the 'txtPro_ID' but still
nothing
happens.
There are no errors when I run the code...just none of the info
fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If not,
use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working
right.
I
keep
getting a compile error on the strSQL line. Am I doing
something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM
[prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is
called
Pro_ID.
I
would like to use whatever number is entered in that field
to
run
a
query
that fills in the rest of the fields with information
associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an
SQL
statement
to retrieve the values you want and place them in the
appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do
the
same
thing,
although using multiple DLookUps usually contributes a lot
more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" &
Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" &
Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Wow! Something that simple has been tripping me up all this time. Incredible.
Thank you so much for your help--Carl and everyone else who answered my
questions--I really appreciate it.

Carl Rapson said:
Looking back at your original post, I think I can see a problem. Whatever
name you use in your SELECT statement, that's the name you need to use in
your assignment statement. So, for example, if your SQL statement is:

strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE
...."

then the names you need to use in your assignment statements are LAST_NAME
and FIRST_NAME:

Me.txtPro_Last_Name = rs.Fields![LAST_NAME]
Me.txtPro_First_Name = rs.Fields![FIRST_NAME]

The field names must match exactly. So once again, what are the names of the
fields in the table? Open the table in design view and look at the names.
Those are the names that you must have in your SELECT statement and that you
must use in your assignment statement.

Carl Rapson

Yepp said:
Yes, I still get the error after making the changes. It just highlights
the
line in yellow. The names are definitely correct.

Carl Rapson said:
Just as an experiment, try changing the names of the controls on the form
to
something like "txtPro_Last_Name", to differentiate them from the table
field names. Then, use the new control names:

Me.txtPro_Last_Name = rs.Fields![Pro_Last_Name]

Does the error still occur?

Carl Rapson

I am getting a compile error (method or data member not found) and also
the
"item not found in this collection" error. I ran the query by itself
to
test
it and it worked fine. I checked the names to make sure they were
correct...that is what is puzzling.

The names of the controls are Pro Number, Pro Last Name, and Pro First
Name.
The field names from the table are Pro_Number, Pro_Last_Name, and
Pro_First_Name.

:

What errors are you getting? Are you certain you're using the correct
names
for the controls and the table fields? Did you try running the query
standalone? What does your SQL string look like in the Immediate
window?

What are the control names (the Name property in the Properties window
for
each control) and the field names (from the table)?

Carl Rapson

Carl,

I put breaks in the code and keep getting errors on the lines in the
if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the text
box's
control name> = rs.Fields![the field name of the text box].

:

Put a breakpoint in your code and step through each line, checking
the
values in the Immediate window. Is the AfterUpdate event actually
firing
(if
not, you'll never reach your breakpoint)? What does your SQL string
look
like after you build it? Is the recordset actually being populated?
Are
there values in the recordset fields? One thing I do frequently is
print
the
SQL string in the Immediate window, then copy the SQL statement and
paste
it
into an empty Query window (in SQL view). If the query doesn't run,
I
can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in
the
table)
text or numeric? If it's text, even if you're storing a number
you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity,
here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Carl,

I used my control name instead of the 'txtPro_ID' but still
nothing
happens.
There are no errors when I run the code...just none of the info
fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If not,
use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working
right.
I
keep
getting a compile error on the strSQL line. Am I doing
something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM
[prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is
called
Pro_ID.
I
would like to use whatever number is entered in that field
to
run
a
query
that fills in the rest of the fields with information
associated
with
that
Pro_ID. Any ideas as to how I can achieve this? Thanks.

In the AfterUpdate event of the Pro_ID control, construct an
SQL
statement
to retrieve the values you want and place them in the
appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]=" &
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do
the
same
thing,
although using multiple DLookUps usually contributes a lot
more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" &
Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" &
Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Glad to help. Sorry it took so long for me to spot the problem. It's amazing
how the simple things are the easiest to overlook.

Carl Rapson

Yepp said:
Wow! Something that simple has been tripping me up all this time.
Incredible.
Thank you so much for your help--Carl and everyone else who answered my
questions--I really appreciate it.

Carl Rapson said:
Looking back at your original post, I think I can see a problem. Whatever
name you use in your SELECT statement, that's the name you need to use in
your assignment statement. So, for example, if your SQL statement is:

strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM [prov1] WHERE
...."

then the names you need to use in your assignment statements are
LAST_NAME
and FIRST_NAME:

Me.txtPro_Last_Name = rs.Fields![LAST_NAME]
Me.txtPro_First_Name = rs.Fields![FIRST_NAME]

The field names must match exactly. So once again, what are the names of
the
fields in the table? Open the table in design view and look at the names.
Those are the names that you must have in your SELECT statement and that
you
must use in your assignment statement.

Carl Rapson

Yepp said:
Yes, I still get the error after making the changes. It just
highlights
the
line in yellow. The names are definitely correct.

:

Just as an experiment, try changing the names of the controls on the
form
to
something like "txtPro_Last_Name", to differentiate them from the
table
field names. Then, use the new control names:

Me.txtPro_Last_Name = rs.Fields![Pro_Last_Name]

Does the error still occur?

Carl Rapson

I am getting a compile error (method or data member not found) and
also
the
"item not found in this collection" error. I ran the query by
itself
to
test
it and it worked fine. I checked the names to make sure they were
correct...that is what is puzzling.

The names of the controls are Pro Number, Pro Last Name, and Pro
First
Name.
The field names from the table are Pro_Number, Pro_Last_Name, and
Pro_First_Name.

:

What errors are you getting? Are you certain you're using the
correct
names
for the controls and the table fields? Did you try running the
query
standalone? What does your SQL string look like in the Immediate
window?

What are the control names (the Name property in the Properties
window
for
each control) and the field names (from the table)?

Carl Rapson

Carl,

I put breaks in the code and keep getting errors on the lines in
the
if
statement:
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]

I changed the fields to:
Me.Pro_Last_Name = rs.Fields![Pro_Last_Name]
Me.Pro_First_Name = rs.Fields![Pro_First_Name]

Is my syntax off, or am I missing something? I'm using Me.<the
text
box's
control name> = rs.Fields![the field name of the text box].

:

Put a breakpoint in your code and step through each line,
checking
the
values in the Immediate window. Is the AfterUpdate event
actually
firing
(if
not, you'll never reach your breakpoint)? What does your SQL
string
look
like after you build it? Is the recordset actually being
populated?
Are
there values in the recordset fields? One thing I do frequently
is
print
the
SQL string in the Immediate window, then copy the SQL statement
and
paste
it
into an empty Query window (in SQL view). If the query doesn't
run,
I
can
usually catch some errors there.

One important question: is the data type of the Pro_ID field (in
the
table)
text or numeric? If it's text, even if you're storing a number
you'll
need
to put quotes around the value in the SQL string you build:

strSQL = "...WHERE [Pro_ID]='" & Me.<your control name> & "'"

Note the single quotes around the value of Pro_ID. For clarity,
here's
the
code with spaces inserted:

WHERE [Pro_ID] = ' " & Me.<your control name> & " ' "

Carl Rapson

Carl,

I used my control name instead of the 'txtPro_ID' but still
nothing
happens.
There are no errors when I run the code...just none of the
info
fills
in.

:

Is the control (not the field) really named 'txtPro_ID'? If
not,
use
your
own control name there.

Carl Rapson

Carl,

I tried your way (see below) but something is not working
right.
I
keep
getting a compile error on the strSQL line. Am I doing
something
wrong?
==================================
Private Sub Pro_ID_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [prov1].LAST_NAME, [prov1].FIRST_NAME FROM
[prov1]
WHERE
[Pro_ID]=" & Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtLAST_NAME = rs.Fields![ LAST NAME]
Me.txtFIRST_NAME = rs.Fields![FIRST NAME]
End If
rs.Close
Set rs = Nothing
=================================
:

I have a form that has several fields, one of which is
called
Pro_ID.
I
would like to use whatever number is entered in that
field
to
run
a
query
that fills in the rest of the fields with information
associated
with
that
Pro_ID. Any ideas as to how I can achieve this?
Thanks.

In the AfterUpdate event of the Pro_ID control, construct
an
SQL
statement
to retrieve the values you want and place them in the
appropriate
controls:

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT field1,field2 FROM table WHERE [Pro_ID]="
&
Me.txtPro_ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Me.txtField1 = rs.Fields![field1]
Me.txtField2 = rs.Fields![field2]
End If
rs.Close
Set rs = Nothing

Alternately, you could use a series of DLookUp calls to do
the
same
thing,
although using multiple DLookUps usually contributes a lot
more
overhead
(and is slower) than using a single recordset:

Me.txtField1 = DLookUp("field1","table","[Pro_ID]=" &
Me.Pro_ID)
Me.txtField2 = DLookUp("field2","table","[Pro_ID]=" &
Me.Pro_ID)

Carl Rapson
Click to expand...
Click to expand...
Click to expand...
 
Back
Top