autofilling a field based on values of 6 other fields

[babs]

From Previous Post

I have a table that has a delivery yard, pickup yard, and type of
material(2choices). Based on that combination of what is in those three
fields there is a given RATE. On the form based on this table I would like
the rate to automatically fill based on the combination of the 3 fields
chosen. Seems like it should be easy not sure where I go with this.

Thanks,
Barb

John Vinson 8/31/2006 12:54 PM PST

I have a table that has a delivery yard, pickup yard, and type of
material(2choices). Based on that combination of what is in those three
fields there is a given RATE. On the form based on this table I would like
the rate to automatically fill based on the combination of the 3 fields
chosen. Seems like it should be easy not sure where I go with this.

Thanks,
Barb

I'd suggest creating a Rates table with four fields - these three as a
joint Primary Key and the corresponding rate. You can then create a
Query joining your current table to this rate table by the three
fields (two yards and a type) to look up the rate.

John W. Vinson[MVP]

babs 10/9/2006 11:30 AM PST



I did what was recommended below but am having an issue when trying to get
the rate pulled into a tankentryform -based on a query. Not sure what the
query should look like now?? Tried Dlookup on form but having issues with
when delvydnum same with differrent delivydnames. keeps pulling in 1st one
it sees?????

Actually need to have the rate dumped in (preput in- not a calculated
rate)in a RATEtable. Want the rate automatically put in based on actually 6
Fields not just 4 Clientid,pickupydnum,pickupydname, delivydnum, delivydname,
& typeofmat

I am having an issues with having
the same yard numbers for different del yard NAMES

Example:

Prairie Material’s yard # 15(Delivydnum) is Addison(delivydName)

Ozinga Concrete’s yard #15(Delivydnum) is Hampshire(Delivydname).

See prior posting

I have a table that has a delivery yard, pickup yard, and type of
material(2choices). Based on that combination of what is in those three
fields there is a given RATE. On the form based on this table I would like
the rate to automatically fill based on the combination of the 3 fields
chosen. Seems like it should be easy not sure where I go with this.

Thanks,
Barb

I'd suggest creating a Rates table with four fields - these three as a
joint Primary Key and the corresponding rate. You can then create a
Query joining your current table to this rate table by the three
fields (two yards and a type) to look up the rate.

John W. Vinson[MVP]


thanks,
Barb

 

[Duane Hookom]

Do you have a rate table that is based on delivery yard, pickup yard, and
type of material(2choices)?

 

[babs]

Yes,

The rate in the rate table is an input value(not calculated) but based on
the clientid, delivyd#, delivydNAME, pickupyd#, pickupdydNAMe, and typeof
mat(2choices) and can have the same delivYd# but two different deivydNames
(see below) also same for pickupyds.

Thanks for helping,
Barb

 

[Duane Hookom]

You are making this too difficult for me. Just provide a table name with
field names of your rates table and how these fields match up with the
delivery yard, pickup yard, and type of material(2choices).

Also, I assume you are attempting to do this in a form so if the control
names in your form are different from your field names, let us know.

 

[babs]

I have 3 tables

CLIENTTABLE
clientid-PK
clientnumb
clientname
address
state
etc

TBLTANKRATES2
rateid
clientid-pk
pickupyd-pk
pickupydname
delivyd-pk
delivydname
typeofmat-pk
rate

TANKTICKETENTRY
clientid-pk
ticket-pk
servicedate
typeofmat
pickupdyd
delivyd
truck#
gross weight
comment

I need to grab the rate value from the tanksrate2 table where the
clientid,pickupyd, delivyd, and typeofmat are the same.

Joins for query clienttable (clientid-clientid)tankticketentry
tbltankrates2 (clientid-clientid)tankticketntry
tbltankrates2 (pickupyd-pickupyd)tankticketntry
tbltankrates2 (delivyd-delivyd)tankticketntry
tbltankrates2 (typeofmat-typeofmat)tankticketntry

The query worked fine except there may is a situation where there is a
pickup yd with the same # but different pickupydname and same for deliv.

So I made the pickupydname and delivydname primary keys also in the tankrate2
table and now the query is not updatable.

Not sure how to lay this out.

The rate is not a calculated field just based on the clientid,
pickupyd,pickupydname,delivyd,delivydname, and typeofmat. want on the form
based on this query for the rate to automatically fill in based on all of
these items.

On the Form the Textboxes are as follows

textbox fieldname
cboClientid Clientid
clientname clientname(automatically dumped in)
tankmat tankmat(dropdown of 2 choices)
cbopickupydnumb populate an sql after update event based on the clientid
selected
want to only see pickupydnumb from tbltanksrate
that have this clientid so don't have to see ALL
pickupydnumb
cbidelivydnumb same as pickupydnumb(populate based on client id selected)
txtPickkupydname want it to be automatically put in after select drop down
and if two
#15 pickupd yard want correct NAME of one I
select from drop down. right now just grabs 1st one
it sees.
txtdelivydname same as previous

after all of these items are input it should grab the rate field from the
tbltanksrate2 table(query) field name and text box is RAte.

Thanks for your help,
Barb

 

[Duane Hookom]

I would probably write a small user-defined function using DAO like:
Function GetRate(lngClientID as Long, lngPickupyd As Long, _
lngDelivyd as Long, ---etc--- ) as Double
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String
Set db = CurrentDb
strSQL = "SELECT Rate FROM TBLTANKRATES2 " & _
"WHERE ClientID =" & lngClientID & _
" AND pickupyd = " & lngPickupyd & _
" AND delivyd =" & lngdelivyd & _
---etc---
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF and rs.BOF Then
GetRate = rs.Rate
End If
rs.Close
set rs = nothing
set db = Nothing
End Function

Then you can use code on your form after all the fields have been populated
like:
Me.txtSomeTextBox = GetRate(Me.cboClientid, Me. ---etc---)

 

[babs]

I did what you recommended - I am sure I have something wrong here. I
attached it to the afterupdate of the last input field. It is going to this
code in VB and saying


Expected variable or procedure, not module

Private Sub cboDelivyd_AfterUpdate()
Me.txtCartageRate = getrate(Me.cboClientId, Me.TankMat, Me.cboPickupYd,
Me.cboDelivYd, Me.txtDelivydname, Me.txtPickupydname)




This it the function - named the module getrate(not sure if correct place to
do the function)

Option Explicit

Function getrate(lngClientID As Long, lngPickupydnum As Long, lngDelivydnum
As Long, strpickupydname As String, strdelivydname As String, strType As
String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT Rate FROM TBLTANKsRATE2 " & "WHERE ClientID =" &
lngClientID & " AND pickupydnum = " & lngPickupydnum & " AND delivydnum =" &
lngDelivydnum & " AND pickupydname =" & strpickupydname & " AND delivydname
=" & strdelivydname & " AND type =" & strType
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF And rs.BOF Then
getrate = rs.rate
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
End Sub




Also still not sure how the same deliv yd number with different name right
now have dlookup and just grabs 1st delivname it sees????


Thanks so much for keeping to help,
Barb

 

[Duane Hookom]

Any string variables would need quotes to delimit like:
....
.... AND pickupydname =""" & strpickupydname & """ AND delivydname
....

 

[babs]

Duane,

Thanks again for helping. I change the string variables but still getting
the same error after update the delivydnumb:

code in VB and saying
Expected variable or procedure, not module

Private Sub cboDelivyd_AfterUpdate()
Me.txtCartageRate = getrate(Me.cboClientId, Me.TankMat, Me.cboPickupYd,
Me.cboDelivYd, Me.txtDelivydname, Me.txtPickupydname

Also still not sure how to hand the delivydname when same delivyd numb.
keeps grabbing 1st name it sees with the dlookup

Thanks,
Barb

 

[Duane Hookom]

Did you name your module different from the function?

 

[babs]

Duane,

Yes I had them named the same name. I changed the module name to modgetrate
but now get a different error Type Mismatch. and goes to this line. As I
point to each item it shows the actual value that is on the form except the
when I point to me.txtcartagerate it says is Null.

Me.txtCartageRate = getrate(Me.cboClientId, Me.TankMat, Me.cboPickupYd,
Me.cboDelivYd, Me.txtDelivydname, Me.txtPickupydname)



I didn't define the rate field in the function not sure if I need to????



Thanks,
Barb

 

[Duane Hookom]

Look at the data types from your function
lngClientID As Long,
lngPickupydnum As Long,
lngDelivydnum As Long,
strpickupydname As String,
strdelivydname As String,
strType As String
Confirm these.

 

[babs]

The one data type for clientid was a string and I changed it. Still getting
the Type mismatch error and when I point to me.txtcartagerate it says is Null.

Thanks again,
Barb

 

[Duane Hookom]

txtCartagerate will be null since it is on the left side of the expression.
The issue is something to the right of your "=".

 

[babs]

Wanted to post the module modgetrate to see if you could see where it is
going wrong.

Thanks again for helping,
Barb

Function getrate(lngPickupyd As Long, lngDelivyd As Long, strClientID As
String, strpickupydname As String, strdelivydname As String, strType As
String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT Rate FROM TBLTANKsRATE2 " & "WHERE ClientID = """ &
strClientID & " AND pickupyd = " & lngPickupyd & " AND delivyd =" &
lngDelivyd & " AND pickupydname = """ & strpickupydname & """ AND
delivydname= """ & strdelivydname & """ AND type = """ & strType


Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF And rs.BOF Then
getrate = rs.rate
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

 

[Duane Hookom]

Did you attempt to compile your code (see
http://www.access.hookom.net/Articles.htm)? If the code compiles and you get
the data type error, I expect you have set some of the data types wrong. I
can't see your tables and you haven't ever provided the data types of your
fields. Also you will get the error if any of the arguments are null.

 

[babs]

I don't understand why the txtCartagerate should be null- I think it should
reflect the rate value from the tbltanksrate2 since all of the other text
boxes on the right side of the equation reflex the actual value put into the
form.

As I point to each item it shows the actual value that is on the form except
the
when I point to me.txtcartagerate it says is Null.

Me.txtCartageRate = getrate(Me.cboClientId, Me.TankMat, Me.cboPickupYd,
Me.cboDelivYd, Me.txtDelivydname, Me.txtPickupydname)

I verified that the text box names are correct could it be something with
the cartage rate field???

readding the functioncode to see if you can see where I am going wrong. So
confused??

Function getrate(lngPickupyd As Long, lngDelivyd As Long, strClientID As
String, strpickupydname As String, strdelivydname As String, strType As
String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT Rate FROM TBLTANKsRATE2 " & "WHERE ClientID = """ &
strClientID & " AND pickupyd = " & lngPickupyd & " AND delivyd =" &
lngDelivyd & " AND pickupydname = """ & strpickupydname & """ AND
delivydname= """ & strdelivydname & """ AND type = """ & strType


Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF And rs.BOF Then
getrate = rs.rate
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Thanks for still helping - really want to get this.
Barb

 

[Duane Hookom]

Read my reply from last night and come back with some answers so I can help
you.

 

[babs]

Duane,

I did catch that the data type for the clientid should have been text and
changed it - see just prior posting to see if you can tell what may be wrong.

Thanks,
Barb

Please let me know if you have any questions- not sure where else to look
why the actual rate not going into the txtcartagerate text box on the form -
when ALL of the values on the right side when pointed to have the ACTUAL
value show up in the yellow box as point to it in debug mode???

Thanks,
Barb

 

[Duane Hookom]

Your values/arguments sent to the function must be in the same order as
stated in the function. You have
Function getrate(lngPickupyd As Long,....
but are sending in "Me.cboClientId" as the first argument. The order must
match.