Populate form fields

[Doug]

I have a form that I would like to use to populate values
to a table. One of the fields should be populated with a
value based on data entered in 3 fields of the same form.
I have created the following code for a 'got focus' event
for this field, however whenever I get to this field in
the form nothing happens.

What am I doing wrong?

Thanks in advance for any help you can provide.

Private Sub RatePerMile_GotFocus()
Dim Rate As Variant
Dim strloadsite As String
Dim strshipto As String
Dim strtrailer As String

strloadsite = Me.load_site
strshipto = Me.Ship_To_State
strtrailer = Me.Trailer_Type


Rate = "Select [rate] from FrtPrgRates where [state] =
strshipto And [location] = strloadsite And [trailer] =
strtrailertype;"

End Sub

 

[William Taylor]

Syntax problems and others:
solution:
If Rate is a field in FrtPrgRates table or query
and assuming Rate is a control on form where the value is to be placed
Rate = DLookup("Rate", "FrtPrgRates", [state] = " & " ' " & _
Me.Ship_To_State & " ' " & "And [location] = " & " ' " & Me.load_site & _
" ' " & "And [trailer] = " & " ' " & Me.Trailer_Type & " ' ")

I have a form that I would like to use to populate values
to a table. One of the fields should be populated with a
value based on data entered in 3 fields of the same form.
I have created the following code for a 'got focus' event
for this field, however whenever I get to this field in
the form nothing happens.

What am I doing wrong?

Thanks in advance for any help you can provide.

Private Sub RatePerMile_GotFocus()
Dim Rate As Variant ' why is rate a variant, us the field type from the table
Dim strloadsite As String ' dont need these variables

use the forms textbox values directly

Dim strshipto As String
Dim strtrailer As String

strloadsite = Me.load_site
strshipto = Me.Ship_To_State
strtrailer = Me.Trailer_Type

' this will only put the SQL statement into the Rate control on the form
Rate = "Select [rate] from FrtPrgRates where [state] =
strshipto And [location] = strloadsite And [trailer] =
strtrailertype;"

End Sub

 

[Guest]

The code doesn't actually do anything. It is only telling
your Rate variable to be set to the string you entered.

Sounds like you have a Shipping Details table, AND a
Shipping Info table. Are you attempting to populate a
Rate field in your Shipping Details table with the value
of a Rate field of the Shipping Info table? If so, you
are duplicating this data. It would be easier to include
the unique field(s) of the Shipping Info table in the
Shipping Details table as a foreign key field (after
relating the two tables), and have the Rate available
through this relationship as a lookup.

If you want to keep and populate this field your way, your
code should do more than just setting the value of a
variable to a SELECT statement.

1. You could set the ControlSource property of the field
to a DLookup function (but the syntax is a pain).

2. You can have your code actually pull the data from the
table, which could be done with a recordset object.
Dim db as Database
Dim rs as Recordset
Dim strSQL as String
'Include your other string variables and code as well

Set db = currentdb
strSQL = "Select [rate] from FrtPrgRates where [state] =
strshipto And [location] = strloadsite And [trailer] =
strtrailertype;
Set rs = db.OpenRecordset(strsql)
Me.RatePerMile = rs.Fields("Rate")

 

[Doug]

Problem solved. Thanks to all for the help.

-----Original Message-----
Syntax problems and others:
solution:
If Rate is a field in FrtPrgRates table or query
and assuming Rate is a control on form where the value is to be placed
Rate = DLookup("Rate", "FrtPrgRates", [state] = " & " ' " & _
Me.Ship_To_State & " ' " & "And [location] = " & " ' " & Me.load_site & _
" ' " & "And [trailer] = " & " ' " & Me.Trailer_Type & " ' ")

I have a form that I would like to use to populate values
to a table. One of the fields should be populated with a
value based on data entered in 3 fields of the same form.
I have created the following code for a 'got focus' event
for this field, however whenever I get to this field in
the form nothing happens.

What am I doing wrong?

Thanks in advance for any help you can provide.

Private Sub RatePerMile_GotFocus()
Dim Rate As Variant ' why is

rate a variant, us
the field type from the table

Dim strloadsite As String ' dont need

these variables
use the forms textbox values directly

Dim strshipto As String
Dim strtrailer As String

strloadsite = Me.load_site
strshipto = Me.Ship_To_State
strtrailer = Me.Trailer_Type

' this will only put the SQL statement into the

Rate control on the
form

Rate = "Select [rate] from FrtPrgRates where [state] =
strshipto And [location] = strloadsite And [trailer] =
strtrailertype;"

End Sub

.