Using data from a table other than the one linked to a form

[Isis]

If I have an open form displaying controls linke to say a 'Customer' table
and I want to reference some data in another Table - say 'Control' - How do
I reference the 'Control' data and do I need to initiate the 'Control'
record before referencing it's fields in VB code ? If it does need to be
referenced, does it only need referencing once however many times I may
reference the fields in VB on the same form. Does it make a difference if
the Table is not 'related' to the first Table (in the form) but just holds
default info that I might want to use.

Any help appreciated.

Thanks

 

[Albert D.Kallal]

Does it make a difference if
the Table is not 'related' to the first Table (in the form) but just holds
default info that I might want to use.


Well, in MOST cases, if the data is related, then using a sub form really
helps. I talk about sub-forms here
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

I want to reference some data in another Table - say 'Control' - How do
I reference the 'Control' data

Well, it really depends. It is quite un-likely the data is un-related. I
mean, what record, or data do you want from the table called control? (it
can't be random!!).

You can pluck one value from another table using dlookup()

dlookup("field name", "table name", "conditions")

So, you could palce a control on a form, and then make the source of the
control as follows

=dlookup("Color","tblParts","partID = 1234")

The above would pull the color field from a table called tblParts, and the
part id would be 1234.

However, most of the time, you will use sub-forms, or perhaps a listbox...

Here is some additional screen shots of some forms that shows data from MORE
then one table..but, you can see that most of the time the data is
related....

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Isis]

the Table is not 'related' to the first Table (in the form) but just
holds default info that I might want to use.


Well, in MOST cases, if the data is related, then using a sub form
really helps. I talk about sub-forms here
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html


Well, it really depends. It is quite un-likely the data is un-related.
I mean, what record, or data do you want from the table called
control? (it can't be random!!).

You can pluck one value from another table using dlookup()

dlookup("field name", "table name", "conditions")

So, you could palce a control on a form, and then make the source of
the control as follows

=dlookup("Color","tblParts","partID = 1234")

The above would pull the color field from a table called tblParts, and
the part id would be 1234.

However, most of the time, you will use sub-forms, or perhaps a
listbox...

Here is some additional screen shots of some forms that shows data
from MORE then one table..but, you can see that most of the time the
data is related....

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Thanks for the reply again Albert. My control Table will hold defaults
for Paths, Field Insert Defaults and the like - So I would like to be
able to use the COntrol Fields in VB code - something like this;

FileName = Trim([CONTROLefaultSavePath]) + Trim(FileName)
Open (strFileName) For Output As #1

So using the saved Path to create this particular file in the right place
- as opposed to displaying the Control Fields on a form.

Most of the time the fields in my Control File will NOT be related to any
data in other Tables - in fact the control file will be a single record
file with many fields.

Does any of that change the way I reference the fields in the COntrol
File in VB ? Do I need to do a dlookup() before referencing any of the
Control fields in my Code ? - can I continue to reference other Control
Table Fileds without doing another dlookup ?

Thanks again.

Regards

 

[Albert D.Kallal]

Thanks for the reply again Albert. My control Table will hold defaults

for Paths, Field Insert Defaults and the like - So I would like to be
able to use the COntrol Fields in VB code - something like this;

FileName = Trim([CONTROLefaultSavePath]) + Trim(FileName)
Open (strFileName) For Output As #1

You have use dlookup, or open a form attached to that table, or use what we
call a reocrdset...

I suggested dlookup(), as it is often a handy way to grab data...

Assuming only ONE record in your table of defaults, then to get the
defaltsavepath, you can go

strFileName = dlookup("DefaultSavePath","tblDefaults") + Trim(FileName)

The above dlookup has not conditions, and thus the first (and likely only)
record will be returned.

Dlookup can be a bit clumsy however. If you have MANY places in your code to
use values from that ONE record in the tblDefaults, then I would suggest you
use what is called a reocrdSet. A recordSet is the STANDARD way to grab
values from a table.


dim rstDefaults as dao.RecordSet

set rstDefaults = currentdb.openrecordSet("tblDefaults")


now, you can go

rstDefaults!DefaultsSavepath

or

rstDefaults!DefaultAreaCode

etc. etc. in your code. So, the above is less code for each additional use
of defaultsSavePath, or any other value you have in the defaults table.

Do I need to do a dlookup() before referencing any of the
Control fields in my Code ?

Well, in fact, for EACH time you need a particular value, you would have to
use a complete new dlookup() expression. It would seem that using a
reocrdset is a much better approach......

You could even open a global defined reocrdset...and thus use it anywhere in
code.....

The standard code loop to process a talbe of data in codee is

dim strSql as string
dim rstData as dao.RecordSet

strSql = "select * from tblCustomers where City = 'Edmonton'"

set rstData = currentdb.openrecordset(strSql

do while rstData.EOF = false
debug.print rstData!CompanyName
....do whatever you want here...
rstData.MoveNext
loop
rstData.Close
set rstData = nothing

So, the above is a program code loop that would process each record in the
table. In you case,you only have ONE record with all the defaults. Still,
defining and using a reocrdset will be much more easy then using dlookup()