Default Values based on Value in another Table

[Alberto]

Here's what I need to do:
Prompt the user to enter a ModelID in TextBox1 and have the ModelRetailPrice
for that ModelID returned in TextBox2 - both the ModelID and ModelRetailPrice
are in Table1. Once the user does this they can enter data in TextBox3 and
TextBox4. I then want TextBox1, TextBox2, TextBox3 and TextBox4 to be stored
in Table 2. How do I do this?

 

[Allen Browne]

Use the AfterUpdate event procedure of Textbox1 to DLookup() the price of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order Details
subform, the ProductID combo has this kind of AfterUpdate event procedure to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Alberto]

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost", "Table 1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in Criteria
Expression.

 

[Alberto]

I should also add that it says the following in the error message:
Run-time error: '3464'

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost", "Table 1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in Criteria
Expression.

Use the AfterUpdate event procedure of Textbox1 to DLookup() the price of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order Details
subform, the ProductID combo has this kind of AfterUpdate event procedure to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Allen Browne]

Try breaking it down into steps, to see where the problem lies.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.ModelID) Then
strWhere = "ModelID = "& Me.ModelID
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)
If Not IsNull(varResult) Then
Me.ModelRetailCost = varResult
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost", "Table 1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in Criteria
Expression.

Use the AfterUpdate event procedure of Textbox1 to DLookup() the price of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order Details
subform, the ProductID combo has this kind of AfterUpdate event procedure
to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Alberto]

I still received the same error. This time in this line:
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)

I also tried doing it this way:

Private Sub ModelRetailPrice_AfterUpdate()
DLookup("ModelretailPrice","tblModel", "ModelID = '" &
Forms![frmQuotes]!ModelID & "'")
End Sub

but when I did, nothing happened.

Try breaking it down into steps, to see where the problem lies.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.ModelID) Then
strWhere = "ModelID = "& Me.ModelID
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)
If Not IsNull(varResult) Then
Me.ModelRetailCost = varResult
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost", "Table 1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in Criteria
Expression.

Use the AfterUpdate event procedure of Textbox1 to DLookup() the price of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order Details
subform, the ProductID combo has this kind of AfterUpdate event procedure
to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Here's what I need to do:
Prompt the user to enter a ModelID in TextBox1 and have the
ModelRetailPrice
for that ModelID returned in TextBox2 - both the ModelID and
ModelRetailPrice
are in Table1. Once the user does this they can enter data in TextBox3
and
TextBox4. I then want TextBox1, TextBox2, TextBox3 and TextBox4 to be
stored
in Table 2. How do I do this?

 

[Allen Browne]

So if yuo open your table in desigtn view, what Type is ModelID?

If it is Text (not Number), you need extra quotes:
strWhere = "ModelID = """ & Me.ModelID & """"

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I still received the same error. This time in this line:
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)

I also tried doing it this way:

Private Sub ModelRetailPrice_AfterUpdate()
DLookup("ModelretailPrice","tblModel", "ModelID = '" &
Forms![frmQuotes]!ModelID & "'")
End Sub

but when I did, nothing happened.

Try breaking it down into steps, to see where the problem lies.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.ModelID) Then
strWhere = "ModelID = "& Me.ModelID
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)
If Not IsNull(varResult) Then
Me.ModelRetailCost = varResult
End If
End If

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost", "Table
1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in
Criteria
Expression.




:

Use the AfterUpdate event procedure of Textbox1 to DLookup() the price
of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order
Details
subform, the ProductID combo has this kind of AfterUpdate event
procedure
to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Here's what I need to do:
Prompt the user to enter a ModelID in TextBox1 and have the
ModelRetailPrice
for that ModelID returned in TextBox2 - both the ModelID and
ModelRetailPrice
are in Table1. Once the user does this they can enter data in
TextBox3
and
TextBox4. I then want TextBox1, TextBox2, TextBox3 and TextBox4 to
be
stored
in Table 2. How do I do this?

 

[Alberto]

Now when I click on ModelID in the Form, nothing happens. It's as if there
were no code. I don't get an error but I can go on to the rest of the form
and make other changes.

So if yuo open your table in desigtn view, what Type is ModelID?

If it is Text (not Number), you need extra quotes:
strWhere = "ModelID = """ & Me.ModelID & """"

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I still received the same error. This time in this line:
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)

I also tried doing it this way:

Private Sub ModelRetailPrice_AfterUpdate()
DLookup("ModelretailPrice","tblModel", "ModelID = '" &
Forms![frmQuotes]!ModelID & "'")
End Sub

but when I did, nothing happened.

Try breaking it down into steps, to see where the problem lies.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.ModelID) Then
strWhere = "ModelID = "& Me.ModelID
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)
If Not IsNull(varResult) Then
Me.ModelRetailCost = varResult
End If
End If

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost", "Table
1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in
Criteria
Expression.




:

Use the AfterUpdate event procedure of Textbox1 to DLookup() the price
of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order
Details
subform, the ProductID combo has this kind of AfterUpdate event
procedure
to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Here's what I need to do:
Prompt the user to enter a ModelID in TextBox1 and have the
ModelRetailPrice
for that ModelID returned in TextBox2 - both the ModelID and
ModelRetailPrice
are in Table1. Once the user does this they can enter data in
TextBox3
and
TextBox4. I then want TextBox1, TextBox2, TextBox3 and TextBox4 to
be
stored
in Table 2. How do I do this?

 

[Allen Browne]

Temporarily include the line:
Stop
after the strWhere = ... but before the DLookup().

When it stops, open the Immediate Window (Ctrl+G), and examine what's going
on.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Now when I click on ModelID in the Form, nothing happens. It's as if
there
were no code. I don't get an error but I can go on to the rest of the
form
and make other changes.

So if yuo open your table in desigtn view, what Type is ModelID?

If it is Text (not Number), you need extra quotes:
strWhere = "ModelID = """ & Me.ModelID & """"

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

I still received the same error. This time in this line:
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)

I also tried doing it this way:

Private Sub ModelRetailPrice_AfterUpdate()
DLookup("ModelretailPrice","tblModel", "ModelID = '" &
Forms![frmQuotes]!ModelID & "'")
End Sub

but when I did, nothing happened.




:

Try breaking it down into steps, to see where the problem lies.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.ModelID) Then
strWhere = "ModelID = "& Me.ModelID
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)
If Not IsNull(varResult) Then
Me.ModelRetailCost = varResult
End If
End If

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost",
"Table
1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in
Criteria
Expression.




:

Use the AfterUpdate event procedure of Textbox1 to DLookup() the
price
of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order
Details
subform, the ProductID combo has this kind of AfterUpdate event
procedure
to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Here's what I need to do:
Prompt the user to enter a ModelID in TextBox1 and have the
ModelRetailPrice
for that ModelID returned in TextBox2 - both the ModelID and
ModelRetailPrice
are in Table1. Once the user does this they can enter data in
TextBox3
and
TextBox4. I then want TextBox1, TextBox2, TextBox3 and TextBox4
to
be
stored
in Table 2. How do I do this

 

[Alberto]

I figured it out. I was pointing to the Model field which was a text instead
of pointing to the ModelID which was a number field. Thanks for your help.

So if yuo open your table in desigtn view, what Type is ModelID?

If it is Text (not Number), you need extra quotes:
strWhere = "ModelID = """ & Me.ModelID & """"

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I still received the same error. This time in this line:
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)

I also tried doing it this way:

Private Sub ModelRetailPrice_AfterUpdate()
DLookup("ModelretailPrice","tblModel", "ModelID = '" &
Forms![frmQuotes]!ModelID & "'")
End Sub

but when I did, nothing happened.

Try breaking it down into steps, to see where the problem lies.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.ModelID) Then
strWhere = "ModelID = "& Me.ModelID
varResult = DLookup("ModelRetailCost", "Table 1", strWhere)
If Not IsNull(varResult) Then
Me.ModelRetailCost = varResult
End If
End If

Thanks. I think I'm almost there.

I typed the following into my form:
Forms![frmQuotes]!ModelRetailCost = DLookup("ModelRetailCost", "Table
1",
"ModelID = " & Forms![frmQuotes]!ModelID)

When I ran this I got the following error: Data Type Mismatch in
Criteria
Expression.




:

Use the AfterUpdate event procedure of Textbox1 to DLookup() the price
of
the ModelID in Table2.

For an example, see the Northwind sample database. In the Order
Details
subform, the ProductID combo has this kind of AfterUpdate event
procedure
to
get the price each from the Products table.

If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Here's what I need to do:
Prompt the user to enter a ModelID in TextBox1 and have the
ModelRetailPrice
for that ModelID returned in TextBox2 - both the ModelID and
ModelRetailPrice
are in Table1. Once the user does this they can enter data in
TextBox3
and
TextBox4. I then want TextBox1, TextBox2, TextBox3 and TextBox4 to
be
stored
in Table 2. How do I do this?