Access 2007: Self-populate fields on click of a button

[Courtney G]

Can anyone assist with the following question? I would really appreciate
your help! . . .

 

[Douglas J. Steele]

From the error message you're getting, I'm guessing that id is a text field,
not a numeric one.

Try:

strSql = "select * from tbl_Enquiries where id = '" & Me!EnquiryID & "'"

Exagerated for clarity, that's

strSql = "select * from tbl_Enquiries where id = ' " & Me!EnquiryID & " ' "

 

[Courtney G]

I would like to thank all of you for your assistance; however, I have still
not been able to get the code to work. It currently reads as follows:

Private Sub Command106_Click()
Dim rstCust As DAO.Recordset
Dim strSql As String

strSql = "select * from tbl_Enquiries where EnquiryID = '" & Me!EnquiryID
& "'"
Set rstCust = CurrentDb.OpenRecordset(strSql)

Me.ProjectAddressLine1 = rstCust!PostalAddressLine1
Me.ProjectAddressLine2 = rstCust!PostalAddressLine2
Me.ProjectAddressLine3 = rstCust!PostalAddressLine3
Me.ProjectCity_Town = rstCust!PostalCity
Me.ProjectCounty = rstCust!PostalCounty
Me.ProjectPostCode = rstCust!PostalPostcode
rst.Close


End Sub

After I made some changes using your suggestions. I am still getting the
following error:

Run-time error '3464': Data type mismatch in criteria expression
If I click 'Debug' then the following line is highlighted in the MVBA window:
Set rstCust = CurrentDb.OpenRecordset(strSql)

Thank you in advance if you are able to help.

I can't see any obvious error in your code. Often this error arises if the
parameter reference is hard coded into the string expression such as :

strSql = "select * from tbl_Enquiries where id = Forms!MyForm!EnquiryID

but you have correctly concatenated the value of EnquiryID with:

strSql = "select * from tbl_Enquiries where id = " & Me!EnquiryID

so it should be fine, but is the primary key of tbl_Enquiries really named id
rather than EnquiryID? If not that could be the problem.

However, I think this might be a 'road to Dublin' question, as it looks to me
as if you may be introducing some redundancy. Are you attempting to assign
the values from the tbl_Enquiries table to columns (fields) in the
tbl_Projects table underlying your current form? If so you don't need to do
so, nor should you as redundancy is not only inefficient, but more
importantly leaves the database at risk of inconsistent data creeping in.
Delete all the address columns from the tbl_Projects table and simply base
you form on a query which joins tbl_Projects to tbl_Enquiries on EnquiryID.
In the query include the address columns from tbl_Enquiries and in the form
include text box controls bound to those columns. The EnquryID control in
the form should be bound to the column from tbl_Projects not that from
tbl_Enquiries BTW.

When an EnquiryID is entered in the form the address values from
tbl_Enquiries will show in the text box controls. If you want to prevent the
values being inadvertently edited set the Locked property of each address
control to True (Yes) and the Enabled property to False (No).

Ken Sheridan
Stafford, England

Can anyone assist with the following question? I would really appreciate
your help! . . .

Thank you for your advice. This is the first time I have tried to work with
code, so please bear with me.

[quoted text clipped - 50 lines]

in the above, customer_id is the forighn key collum used to relate back to
the customers table.

 

[Douglas J. Steele]

Is EnquiryID a numeric field? If so, you need

strSql = "select * from tbl_Enquiries where EnquiryID = " & Me!EnquiryID

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I would like to thank all of you for your assistance; however, I have
still
not been able to get the code to work. It currently reads as follows:

Private Sub Command106_Click()
Dim rstCust As DAO.Recordset
Dim strSql As String

strSql = "select * from tbl_Enquiries where EnquiryID = '" &
Me!EnquiryID
& "'"
Set rstCust = CurrentDb.OpenRecordset(strSql)

Me.ProjectAddressLine1 = rstCust!PostalAddressLine1
Me.ProjectAddressLine2 = rstCust!PostalAddressLine2
Me.ProjectAddressLine3 = rstCust!PostalAddressLine3
Me.ProjectCity_Town = rstCust!PostalCity
Me.ProjectCounty = rstCust!PostalCounty
Me.ProjectPostCode = rstCust!PostalPostcode
rst.Close


End Sub

After I made some changes using your suggestions. I am still getting the
following error:

Run-time error '3464': Data type mismatch in criteria expression
If I click 'Debug' then the following line is highlighted in the MVBA
window:
Set rstCust = CurrentDb.OpenRecordset(strSql)

Thank you in advance if you are able to help.

I can't see any obvious error in your code. Often this error arises if
the
parameter reference is hard coded into the string expression such as :

strSql = "select * from tbl_Enquiries where id = Forms!MyForm!EnquiryID

but you have correctly concatenated the value of EnquiryID with:

strSql = "select * from tbl_Enquiries where id = " & Me!EnquiryID

so it should be fine, but is the primary key of tbl_Enquiries really
named id
rather than EnquiryID? If not that could be the problem.

However, I think this might be a 'road to Dublin' question, as it looks
to me
as if you may be introducing some redundancy. Are you attempting to
assign
the values from the tbl_Enquiries table to columns (fields) in the
tbl_Projects table underlying your current form? If so you don't need to
do
so, nor should you as redundancy is not only inefficient, but more
importantly leaves the database at risk of inconsistent data creeping in.
Delete all the address columns from the tbl_Projects table and simply
base
you form on a query which joins tbl_Projects to tbl_Enquiries on
EnquiryID.
In the query include the address columns from tbl_Enquiries and in the
form
include text box controls bound to those columns. The EnquryID control
in
the form should be bound to the column from tbl_Projects not that from
tbl_Enquiries BTW.

When an EnquiryID is entered in the form the address values from
tbl_Enquiries will show in the text box controls. If you want to prevent
the
values being inadvertently edited set the Locked property of each address
control to True (Yes) and the Enabled property to False (No).

Ken Sheridan
Stafford, England

Can anyone assist with the following question? I would really
appreciate
your help! . . .

:

Thank you for your advice. This is the first time I have tried to
work with
code, so please bear with me.
[quoted text clipped - 50 lines]
in the above, customer_id is the forighn key collum used to relate
back to
the customers table.

 

[Courtney G]

Dear Douglas,

Yes - it is a numeric field. I have changed it and am now getting the
following error: Runtime error 424: Object required.

When I click debug, the rst.Close line is highlighted.

Thank you for your help! I think we're closer to the answer!!

- Courtney

Is EnquiryID a numeric field? If so, you need

strSql = "select * from tbl_Enquiries where EnquiryID = " & Me!EnquiryID

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I would like to thank all of you for your assistance; however, I have
still
not been able to get the code to work. It currently reads as follows:

Private Sub Command106_Click()
Dim rstCust As DAO.Recordset
Dim strSql As String

strSql = "select * from tbl_Enquiries where EnquiryID = '" &
Me!EnquiryID
& "'"
Set rstCust = CurrentDb.OpenRecordset(strSql)

Me.ProjectAddressLine1 = rstCust!PostalAddressLine1
Me.ProjectAddressLine2 = rstCust!PostalAddressLine2
Me.ProjectAddressLine3 = rstCust!PostalAddressLine3
Me.ProjectCity_Town = rstCust!PostalCity
Me.ProjectCounty = rstCust!PostalCounty
Me.ProjectPostCode = rstCust!PostalPostcode
rst.Close


End Sub

After I made some changes using your suggestions. I am still getting the
following error:

Run-time error '3464': Data type mismatch in criteria expression
If I click 'Debug' then the following line is highlighted in the MVBA
window:
Set rstCust = CurrentDb.OpenRecordset(strSql)

Thank you in advance if you are able to help.

I can't see any obvious error in your code. Often this error arises if
the
parameter reference is hard coded into the string expression such as :

strSql = "select * from tbl_Enquiries where id = Forms!MyForm!EnquiryID

but you have correctly concatenated the value of EnquiryID with:

strSql = "select * from tbl_Enquiries where id = " & Me!EnquiryID

so it should be fine, but is the primary key of tbl_Enquiries really
named id
rather than EnquiryID? If not that could be the problem.

However, I think this might be a 'road to Dublin' question, as it looks
to me
as if you may be introducing some redundancy. Are you attempting to
assign
the values from the tbl_Enquiries table to columns (fields) in the
tbl_Projects table underlying your current form? If so you don't need to
do
so, nor should you as redundancy is not only inefficient, but more
importantly leaves the database at risk of inconsistent data creeping in.
Delete all the address columns from the tbl_Projects table and simply
base
you form on a query which joins tbl_Projects to tbl_Enquiries on
EnquiryID.
In the query include the address columns from tbl_Enquiries and in the
form
include text box controls bound to those columns. The EnquryID control
in
the form should be bound to the column from tbl_Projects not that from
tbl_Enquiries BTW.

When an EnquiryID is entered in the form the address values from
tbl_Enquiries will show in the text box controls. If you want to prevent
the
values being inadvertently edited set the Locked property of each address
control to True (Yes) and the Enabled property to False (No).

Ken Sheridan
Stafford, England

Courtney G wrote:
Can anyone assist with the following question? I would really
appreciate
your help! . . .

:

Thank you for your advice. This is the first time I have tried to
work with
code, so please bear with me.
[quoted text clipped - 50 lines]
in the above, customer_id is the forighn key collum used to relate
back to
the customers table.

 

[Douglas J. Steele]

You're opening rstCust, but you're trying to close rst.

Change it to

rstCust.Close

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas,

Yes - it is a numeric field. I have changed it and am now getting the
following error: Runtime error 424: Object required.

When I click debug, the rst.Close line is highlighted.

Thank you for your help! I think we're closer to the answer!!

- Courtney

Is EnquiryID a numeric field? If so, you need

strSql = "select * from tbl_Enquiries where EnquiryID = " & Me!EnquiryID

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I would like to thank all of you for your assistance; however, I have
still
not been able to get the code to work. It currently reads as follows:

Private Sub Command106_Click()
Dim rstCust As DAO.Recordset
Dim strSql As String

strSql = "select * from tbl_Enquiries where EnquiryID = '" &
Me!EnquiryID
& "'"
Set rstCust = CurrentDb.OpenRecordset(strSql)

Me.ProjectAddressLine1 = rstCust!PostalAddressLine1
Me.ProjectAddressLine2 = rstCust!PostalAddressLine2
Me.ProjectAddressLine3 = rstCust!PostalAddressLine3
Me.ProjectCity_Town = rstCust!PostalCity
Me.ProjectCounty = rstCust!PostalCounty
Me.ProjectPostCode = rstCust!PostalPostcode
rst.Close


End Sub

After I made some changes using your suggestions. I am still getting
the
following error:

Run-time error '3464': Data type mismatch in criteria expression
If I click 'Debug' then the following line is highlighted in the MVBA
window:
Set rstCust = CurrentDb.OpenRecordset(strSql)

Thank you in advance if you are able to help.

:

I can't see any obvious error in your code. Often this error arises
if
the
parameter reference is hard coded into the string expression such as :

strSql = "select * from tbl_Enquiries where id =
Forms!MyForm!EnquiryID

but you have correctly concatenated the value of EnquiryID with:

strSql = "select * from tbl_Enquiries where id = " & Me!EnquiryID

so it should be fine, but is the primary key of tbl_Enquiries really
named id
rather than EnquiryID? If not that could be the problem.

However, I think this might be a 'road to Dublin' question, as it
looks
to me
as if you may be introducing some redundancy. Are you attempting to
assign
the values from the tbl_Enquiries table to columns (fields) in the
tbl_Projects table underlying your current form? If so you don't need
to
do
so, nor should you as redundancy is not only inefficient, but more
importantly leaves the database at risk of inconsistent data creeping
in.
Delete all the address columns from the tbl_Projects table and simply
base
you form on a query which joins tbl_Projects to tbl_Enquiries on
EnquiryID.
In the query include the address columns from tbl_Enquiries and in the
form
include text box controls bound to those columns. The EnquryID
control
in
the form should be bound to the column from tbl_Projects not that from
tbl_Enquiries BTW.

When an EnquiryID is entered in the form the address values from
tbl_Enquiries will show in the text box controls. If you want to
prevent
the
values being inadvertently edited set the Locked property of each
address
control to True (Yes) and the Enabled property to False (No).

Ken Sheridan
Stafford, England

Courtney G wrote:
Can anyone assist with the following question? I would really
appreciate
your help! . . .

:

Thank you for your advice. This is the first time I have tried to
work with
code, so please bear with me.
[quoted text clipped - 50 lines]
in the above, customer_id is the forighn key collum used to
relate
back to
the customers table.

 

[Courtney G]

What a novice I am! I really appreciate your help! Now when I click the
button it brings ub the MicrosoftVB screen and there is a MicrosoftVB error
box that reads: Compile error: Invalid outside procedure Options: OK or Help

The MicrosoftVB code form showing on the screen behind the error box is for
another subform also related to frm_Projects. Is it possible that the error
is something to do with that?!?

Thank you again for your help!

You're opening rstCust, but you're trying to close rst.

Change it to

rstCust.Close

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas,

Yes - it is a numeric field. I have changed it and am now getting the
following error: Runtime error 424: Object required.

When I click debug, the rst.Close line is highlighted.

Thank you for your help! I think we're closer to the answer!!

- Courtney

Is EnquiryID a numeric field? If so, you need

strSql = "select * from tbl_Enquiries where EnquiryID = " & Me!EnquiryID

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I would like to thank all of you for your assistance; however, I have
still
not been able to get the code to work. It currently reads as follows:

Private Sub Command106_Click()
Dim rstCust As DAO.Recordset
Dim strSql As String

strSql = "select * from tbl_Enquiries where EnquiryID = '" &
Me!EnquiryID
& "'"
Set rstCust = CurrentDb.OpenRecordset(strSql)

Me.ProjectAddressLine1 = rstCust!PostalAddressLine1
Me.ProjectAddressLine2 = rstCust!PostalAddressLine2
Me.ProjectAddressLine3 = rstCust!PostalAddressLine3
Me.ProjectCity_Town = rstCust!PostalCity
Me.ProjectCounty = rstCust!PostalCounty
Me.ProjectPostCode = rstCust!PostalPostcode
rst.Close


End Sub

After I made some changes using your suggestions. I am still getting
the
following error:

Run-time error '3464': Data type mismatch in criteria expression
If I click 'Debug' then the following line is highlighted in the MVBA
window:
Set rstCust = CurrentDb.OpenRecordset(strSql)

Thank you in advance if you are able to help.

:

I can't see any obvious error in your code. Often this error arises
if
the
parameter reference is hard coded into the string expression such as :

strSql = "select * from tbl_Enquiries where id =
Forms!MyForm!EnquiryID

but you have correctly concatenated the value of EnquiryID with:

strSql = "select * from tbl_Enquiries where id = " & Me!EnquiryID

so it should be fine, but is the primary key of tbl_Enquiries really
named id
rather than EnquiryID? If not that could be the problem.

However, I think this might be a 'road to Dublin' question, as it
looks
to me
as if you may be introducing some redundancy. Are you attempting to
assign
the values from the tbl_Enquiries table to columns (fields) in the
tbl_Projects table underlying your current form? If so you don't need
to
do
so, nor should you as redundancy is not only inefficient, but more
importantly leaves the database at risk of inconsistent data creeping
in.
Delete all the address columns from the tbl_Projects table and simply
base
you form on a query which joins tbl_Projects to tbl_Enquiries on
EnquiryID.
In the query include the address columns from tbl_Enquiries and in the
form
include text box controls bound to those columns. The EnquryID
control
in
the form should be bound to the column from tbl_Projects not that from
tbl_Enquiries BTW.

When an EnquiryID is entered in the form the address values from
tbl_Enquiries will show in the text box controls. If you want to
prevent
the
values being inadvertently edited set the Locked property of each
address
control to True (Yes) and the Enabled property to False (No).

Ken Sheridan
Stafford, England

Courtney G wrote:
Can anyone assist with the following question? I would really
appreciate
your help! . . .

:

Thank you for your advice. This is the first time I have tried to
work with
code, so please bear with me.
[quoted text clipped - 50 lines]
in the above, customer_id is the forighn key collum used to
relate
back to
the customers table.

 

[Douglas J. Steele]

Sorry, but it's pretty difficult to diagnose what's going on given what
you've written.

While in the VB Editor working on the module associated with the form in
question, try compiling the module to see whether it highlights where the
error is.