rate not showing up on form but is in sql

[babs]

Duane or anyone,

Don't know if you saw this since it is so deep into the threads - thought I
would repost this. I feel like I am sooooo close - thanks- and would hate to
try and reexplain the tables and everything. Can you see why I am not
getting the txtcartagerate value in the text box on the form.

Previous posting:

Wow- the datasheet view of the query did show the rate!!! But not showing up
on the FORM


the field name on the tbltanksrate2 is rate and on the form it is an unbound
text box named txtCartageRate. On the afterupdate event of the delivyd is
where i was trying to set the value of the txtcartagerate text box. See
previous posting to understand better. Not sure what is going wrong on the
form???


Thanks again SO MUCH,
Barb


:

Click to show or hide original message or reply text.

 

[Duane Hookom]

I am confused by
"the field name on the tbltanksrate2 is rate and on the form it is an
unbound text box named txtCartageRate"
How can an unbound text box be bound to a field named rate from
tblTanksrate2?

 

[babs]

I changed the txtCartageRate - control source on the form to bound to rate-
which is the field name in tblTanksrate2 and kept the Name of the text box
txtCartagerate.

The event procedure attached to the after update of delivydnum is below.
Private Sub cboDelivyd_AfterUpdate()
Me.txtCartageRate = getrate(Me.cboClientId, Me.cboPickupYd, Me.cboDelivYd,
Me.txtPickupydname, Me.txtDelivydname, Me.TankMat)

End Sub

After I put in the delivydnum this error shows up?????

Cannot enter value into blank field on 'one' side of outer join??

Thanks for your help,
Barb

 

[Duane Hookom]

Is this issue caused by the function or any data entry into the text box?
What happens if you type a value into txtCartageRate?

 

[babs]

It occurs when I enter the delivydnum - the event proc. is tied to the after
update of that combo box. When I just try to enter a value into the
txtcartagerate text box - with control source rate it does not allow me to
enter anything but no error comes up either.

Thanks again,
BArb

 

[Duane Hookom]

If you can't enter anything into the txtCartageRate by typing then the issue
is probably with the record source of your form. If you view the record
source in datasheet view, can you enter/edit values in the field?

 

[babs]

The record source is a query and no I can not add anything to the rate field
in datasheet view.
Also the deliv name and pickupname are automatically input with a Dlookup on
the form and on the query they are not updateable either.

Just reexplaining tables from previous post.

I have 3 tables

CLIENTTABLE
clientid-PK
clientnumb
clientname
address
state
etc

TBLTANKRATES2
rateid
clientid-pk
pickupyd-pk
pickupydname-pk
delivyd-pk
delivydname-pk
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, pickupydname, delivyd, delivydname, and typeofmat are the
same.

Joins for query are as follows
clienttable (clientid-clientid)tankticketentry (innerjoin)

Had to do Outerjoins with all the Primary keys to make the query updateable
-any ideas????

tbltankrates2 (clientid-clientid)tankticketntry (Outerjoin - Include all
records from 'TankTicketEntry' and only those records from 'tblTanksRate'
where joined fields are equal

tbltankrates2 (pickupyd-pickupyd)tankticketntry (Outerjoin - Include all
records from 'TankTicketEntry' and only those records from 'ClientTable'
where joined fields are equal


tbltankrates2 (delivyd-delivyd)tankticketntry (OuterJoin- same as above)
tbltankrates2 (pickupydname-pickupydname)tankticketntry (OuterJoin- same as
above)
tbltankrates2 (delivydname-delivydname)tankticketntry (OuterJoin- same as
above)
tbltankrates2 (typeofmat-typeofmat)tankticketntry (OuterJoin- same as above)

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]

This changes the entire issue in the thread. Here are some links regarding
updateable queries:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp
http://support.microsoft.com/?kbid=328828

If you can't figure out the issue, consider creating a new thread in the
Queries NG.