Add tax exempt # automatically if same job# from previous records

[Guest]

Copy of what was posted before: Didn't get resolved.

Previous records are from the same form that we are on.

Please help,
Thanks,
Barb



I have a form that has records- quotes that are unique by quoteid. For some
of the records there can be the same job# and tax exempt# as a previous
record. The tax exempt# is the same for the given job#. Bottom line if on a
new record a job# from a previous record is selected. I would like the tax
exempt# to be automatically put in. I tried to make a tax# job # table and
make it be the combo box - however too many tables are tied together and
makes the query not updatable. KNow I can just handle it with code maybe
with Me. ... code of some sort.

Please help,
Thanks,
Barb


Klatuu 2/14/2006 10:17 AM PST



No knowing how your data is structured, I can't be specific, but it looks
like you probably have the Job# and tax# together in a record somewhere. You
could use a DLookup to find the tax#:

Dim strTaxID as String
strTaxID = Nz((DLookup("[TaxID]", "YourTableNameHere", "[JobID] = '"
_ & Me!txtJob & "'"),"")
If strTaxId <> vbNullString then
Me.txtTaxId = strTaxId
End If

I have a form that has records- quotes that are unique by quoteid. For some
of the records there can be the same job# and tax exempt# as a previous
record. The tax exempt# is the same for the given job#. Bottom line if on a
new record a job# from a previous record is selected. I would like the tax
exempt# to be automatically put in. I tried to make a tax# job # table and
make it be the combo box - however too many tables are tied together and
makes the query not updatable. KNow I can just handle it with code maybe
with Me. ... code of some sort.

Please help,
Thanks,
Barb

babs 2/15/2006 7:17 AM PST



Thanks for your input. I am getting a syntax error. Not real sure about
the job, job id difference and what goes where. on the form job name combo
box is cbojob and the control source for that combo box is just job. Not
sure what NZ is- can you see anything wrong with my code. I also appear to
be missing a ) and not sure where to put it.

Yes the table that the record is in is tied to the query that is tied to the
form that I am ON.
See code below:
Dim strtaxid As String

strtaxid=Nz((dlookup("[taxex]", "Ticket_Entry_Table", "[job]='" & me!cbojob
& "'"),"")
If strtaxid <> vbNullString Then
Me.Taxex = strtaxid
End If


Thanks for your help,
Barb

No knowing how your data is structured, I can't be specific, but it looks
like you probably have the Job# and tax# together in a record somewhere. You
could use a DLookup to find the tax#:

Dim strTaxID as String
strTaxID = Nz((DLookup("[TaxID]", "YourTableNameHere", "[JobID] = '"
_ & Me!txtJob & "'"),"")
If strTaxId <> vbNullString then
Me.txtTaxId = strTaxId
End If

I have a form that has records- quotes that are unique by quoteid. For some
of the records there can be the same job# and tax exempt# as a previous
record. The tax exempt# is the same for the given job#. Bottom line if on a
new record a job# from a previous record is selected. I would like the tax
exempt# to be automatically put in. I tried to make a tax# job # table and
make it be the combo box - however too many tables are tied together and
makes the query not updatable. KNow I can just handle it with code maybe
with Me. ... code of some sort.

Please help,
Thanks,
Barb

 

[Guest]

have you tried a combo boax to populate the records? It is not the most
efficient way to do it but might be the easiest without sitting down in front
of it.

combo box [job] data source is job# fields are [job#] and [exempt#]
hidden text field: [job#temp] and [exempt#temp]
control source for the two fields are [=job.column(0)] and (1)
create a setvalue quere to set the value of the job# and exempt# from the
temp fields.

The easiest way I can think to do it without looking at it.

if on a new record a job# from a prs record is selected. I would like the
tax