Default but over-rideable values pulled from an unrelated table

[Mike Langensiepen]

I have a MyCompany Information single record tabkle which holds details such
as Company Name, Tax Rates etc.

I currently use dlookups to extract info from this table and use it in forms
and reports however I want to be able to pull in a SalesTaxRate from the
MyCompany Information file as a default but then store this in the main
cleitn data table. This means that if the SalesTaxRate changes, older
records will have the original figures preserved whereas new records will
have the updated rate.

What is the best way of doing this?

Cheers

Mike

 

[Damian S]

Hi Mike,

How about storing the current tax rate in another table, and using that
table to lookup the Tax Rate (which can then be stored with the Company
Record).

Damian.

 

[tina]

you'll need a field in the main data table to hold the values, of course.
you don't say where you're using the DLookup function, so i'll assume you
mean in a form. i'll further assume that you're using an unbound textbox
control in the form, with ControlSource property set to

=DLookup("TaxRate", "MyCompanyInformation")

*when* you look up the value you want depends on what you're doing in the
form. how you look it up is a matter mainly of what's easiest for you; a
DLookup() function is probably as good a way as any. the difference is that
you need to 1) bind a textbox control to the new field in the table that the
form is bound to, then 2) use a macro or VBA code to retrieve the value you
want, and 3) set the value of the bound control to the retrieved value, so
that it will be stored in the current record. in VBA it would be something
like

Me!TextboxControlName = DLookup("TaxRate", _
"MyCompanyInformation")

hth

 

[Mike Langensiepen]

I've already put the tax rate field in the clientdata table ready to hold
the lookup value. However, since what I want is a one-time lookup only the
moment the record is generated. I assume that I can do it with that bit of
code on the before insert event?
..

 

[Mike Lang]

Ok. Here we go. The single record table has a field SalesTaxRate which holds
the current value.

In my main clientdata file I have a field which says GSTRate. In my data
entry form, I have a bound textbox (Text444) with this fieldname (GSTRate)
as my control source.

On the forms beforeinsert event I have the code
Me!Text444=dlookup("SalesTaxRate","tblMyCompanyInformation")

Now I had assumed that the moment I inserted a record, it would dlookup the
default GSTrate and update the field. Because the update only happens on a
recordcreate it should only insert the current value at time of creation
(which is correct). It doesn''t! - what have I done wrong?

 

[tina]

well, let's check the obvious first: did you put the code in the
BeforeInsert event *procedure*? or write it directly on the Before Insert
event *property* line in the Properties box in form design view? if the
latter, delete it and do the former.

if you put the expression in the correct place in the form's VBA module,
then we need more information than "It doesn't!". are you getting an error
message? have you tried stepping through the code to make sure it's firing?
more detail, please.

hth

 

[Mike Langensiepen]

Hi Tina
On the BeforeInsert event line for my data entry form I have an existing
event procedure which creates a Job Number for the event the moment anything
is put in the form. The code for this is

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CustomerID = NewCustNum() where NewCustNum is a
piece of code taken from suggestions by Albert Kallal - this works nicely.
End Sub

I tried adding the
Me!Text401=dlookup("SalesTaxRate","tblMyCompanyInformation")

After the Me!CustomerID and before the end sub

The bound textbox Has a control source of GSTRate which is a field in the
main clientdata file.

I thought that what would happen is that the moment a record is inserted,
the dlookup would pull the value out of the MyCompanyInformation file and
then update the form which would put the value into the GSTRate field.

 

[tina]

okay, you're putting the code in the procedure correctly. on the face of it,
the code looks correct, but we can't see your database. are you using the
correct name of the textbox control? in the DLookup() function, are you
using the correct names of the table, and the field in the table? are you
getting an error message, and if so, what is it? have you tried stepping
through the code? you could also test the DLookup() function by adding a
line above the Me!Textbox... code, as

Debug.Print dlookup("SalesTaxRate","tblMyCompanyInformation")

then running the code and checking the Immediate window to see what value,
if any, was returned by the function.

hth

 

[Mike Lang]

The dlookup works perfectly if I just use it as the control source but
obviously this doesn't update the GSTRate field once only on record
creation. I've checked the name of the textbox control as well.

I'll try the debug line and report back

Thanks for the help!

Incidentally. Now that I have various dlookups and code going on in my form,
when I move from record to record, it seems to 'skip' rather than moving
neatly to the next sequential record. Any idea why?