Referencing a Table from another Table ???

A

Alan

Sorry thought I had problem Sorted

I have a table that keeps information about places of work, Location, Dept
ID etc which I call to my Form using

Public Sub AssignVariables()
Dim rstVar As Recordset
Set rstVar = CurrentDb.OpenRecordset("tblVariables", dbOpenDynaset)
With rstVar
.FindFirst "[DEPTID] = '" & Me.OpenArgs & "'"

OFFICE = .Fields("OFFICES")
AREA = .Fields("LOCATION")
ROLE = .Fields("JOB DESC")
DEPARTMENT = .Fields("DEPTID")
End With
End Sub
The OpenArgs Variable is defined dependant on button press in First Form
when opened.

Currently this information populates variables in my SQL code in the rest of
my procedure. However will not populate if I call SQL Code from another table
using DLookup...

I may again be missing something obvious ... but pulling my hair out
May be my code just has to remain messy ??

Any help again would be gratefully appreciated
 
M

Marshall Barton

Alan said:
I have a table that keeps information about places of work, Location, Dept
ID etc which I call to my Form using

Public Sub AssignVariables()
Dim rstVar As Recordset
Set rstVar = CurrentDb.OpenRecordset("tblVariables", dbOpenDynaset)
With rstVar
.FindFirst "[DEPTID] = '" & Me.OpenArgs & "'"

OFFICE = .Fields("OFFICES")
AREA = .Fields("LOCATION")
ROLE = .Fields("JOB DESC")
DEPARTMENT = .Fields("DEPTID")
End With
End Sub
The OpenArgs Variable is defined dependant on button press in First Form
when opened.

Currently this information populates variables in my SQL code in the rest of
my procedure. However will not populate if I call SQL Code from another table
using DLookup...
Click to expand...


Dlookup only returns a single value so it would be messy to
get all those fields with one call. You could use multiple
calls, but that would be inefficient.

OTOH, a table can not call a function, so I'm not sure what
you're talking about with populating multiple fields???

If you're talking about retrieving those fields for use in a
query, then Join the tables and don't bother with either
function.

BTW, using FindFirst is the long, slow way to do that unless
you're going to get values from other records before closing
the recordset. Try this instead:

Dim strSQL As String
strSQL = "SELECT OFFICES,LOCATION,[JOB DESC],DEPTID " _
& "FROM tblVariables " _
& "WHERE DEPTID = '" & Me.OpenArgs & "'"
Set rstVar = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
With rstVar
OFFICE = .Fields("OFFICES")
AREA = .Fields("LOCATION")
ROLE = .Fields("JOB DESC")
DEPARTMENT = .Fields("DEPTID")
End With
 
A

Alan

Marsh
I have been going round the houses with this for what seems an age ....
I have used the assignvariables routine to populate a query as there are up
to 15 offices within a particular area and I found that messy with all the
SQL in the main body of the procedure ... I take it from your response that
data in one table cannot be used as variables for a query stored in another
table ??

It may be the messy option is the only one, as it works, however will try
the other option you have suggested.

Many thanks for your advice ... im trying to pick up as much of this as I go

Regards


Marshall Barton said:
Alan said:
I have a table that keeps information about places of work, Location, Dept
ID etc which I call to my Form using

Public Sub AssignVariables()
Dim rstVar As Recordset
Set rstVar = CurrentDb.OpenRecordset("tblVariables", dbOpenDynaset)
With rstVar
.FindFirst "[DEPTID] = '" & Me.OpenArgs & "'"

OFFICE = .Fields("OFFICES")
AREA = .Fields("LOCATION")
ROLE = .Fields("JOB DESC")
DEPARTMENT = .Fields("DEPTID")
End With
End Sub
The OpenArgs Variable is defined dependant on button press in First Form
when opened.

Currently this information populates variables in my SQL code in the rest of
my procedure. However will not populate if I call SQL Code from another table
using DLookup...
Click to expand...


Dlookup only returns a single value so it would be messy to
get all those fields with one call. You could use multiple
calls, but that would be inefficient.

OTOH, a table can not call a function, so I'm not sure what
you're talking about with populating multiple fields???

If you're talking about retrieving those fields for use in a
query, then Join the tables and don't bother with either
function.

BTW, using FindFirst is the long, slow way to do that unless
you're going to get values from other records before closing
the recordset. Try this instead:

Dim strSQL As String
strSQL = "SELECT OFFICES,LOCATION,[JOB DESC],DEPTID " _
& "FROM tblVariables " _
& "WHERE DEPTID = '" & Me.OpenArgs & "'"
Set rstVar = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
With rstVar
OFFICE = .Fields("OFFICES")
AREA = .Fields("LOCATION")
ROLE = .Fields("JOB DESC")
DEPARTMENT = .Fields("DEPTID")
End With
Click to expand...
 
M

Marshall Barton

Alan said:
I have been going round the houses with this for what seems an age ....
I have used the assignvariables routine to populate a query as there are up
to 15 offices within a particular area and I found that messy with all the
SQL in the main body of the procedure ... I take it from your response that
data in one table cannot be used as variables for a query stored in another
table ??
Click to expand...


Your question is still not clear to me. If you have a query
for, say, tableA that has a field for DEPTID and you want
the other fields in tblVariables included in the query, then
my suggestion is to Join the two tables in the query:

SELECT tableA.DEPTID,
tableA.otherfield,
. . . ,
tblVariables.OFFICES,
tblVariables.LOCATION,
tblVariables.[JOB DESC]
FROM tableA INNER JOIN tblVariables.
ON tblVariables.DEPTID = tableA.DEPTID

If that's not what you want, then please provide more
information.
 
A

Alan

Marsh

Im sorry for any confusion and thank you for your patience...

tblVariables is as already described as I can make reference to these
variables from my VBA code using the AssignVariables routine already
described.

I have another table calles tblQry which I hoped to tidy my VBA code and
place all my SQL Statements ...
This table includes [QryID] [QryName] & [QryText] .. the text being the SQL
Code eg SELECT * FROM PERSONNEL WHERE ((PERSONNEL.OFFICE)='" & OFFICES &"')

The OFFICES Variable being called dependent on the button selected from an
initial form using Me.MyArgs reference already dicussed.

My problem is that the variables from tblVariable Table do not populate in
the SQL Strings from tblQry when it is called and I am unsure how this is
achieved when the OFFICES Variable is not defined until afther selection in
the first Form ??

I hope this clarifies the matter ... or as I said maybe I am missing
something very obvious ...

Many Thanks again for your time and assistance

Regards


Marshall Barton said:
Alan said:
I have been going round the houses with this for what seems an age ....
I have used the assignvariables routine to populate a query as there are up
to 15 offices within a particular area and I found that messy with all the
SQL in the main body of the procedure ... I take it from your response that
data in one table cannot be used as variables for a query stored in another
table ??
Click to expand...


Your question is still not clear to me. If you have a query
for, say, tableA that has a field for DEPTID and you want
the other fields in tblVariables included in the query, then
my suggestion is to Join the two tables in the query:

SELECT tableA.DEPTID,
tableA.otherfield,
. . . ,
tblVariables.OFFICES,
tblVariables.LOCATION,
tblVariables.[JOB DESC]
FROM tableA INNER JOIN tblVariables.
ON tblVariables.DEPTID = tableA.DEPTID

If that's not what you want, then please provide more
information.
Click to expand...
 
M

Marshall Barton

Alan said:
tblVariables is as already described as I can make reference to these
variables from my VBA code using the AssignVariables routine already
described.

I have another table calles tblQry which I hoped to tidy my VBA code and
place all my SQL Statements ...
This table includes [QryID] [QryName] & [QryText] .. the text being the SQL
Code eg SELECT * FROM PERSONNEL WHERE ((PERSONNEL.OFFICE)='" & OFFICES &"')

The OFFICES Variable being called dependent on the button selected from an
initial form using Me.MyArgs reference already dicussed.

My problem is that the variables from tblVariable Table do not populate in
the SQL Strings from tblQry when it is called and I am unsure how this is
achieved when the OFFICES Variable is not defined until afther selection in
the first Form ??
Click to expand...


Ok, I was really missing the point before.

There are several ways to attack this problem dependong on
how you are running the queries.

If the queries are used as form/report record source, then
change your AssignVariables routine to park the values in
text boxes (named txtOFFICE, etc) on an always open form
(named Variables), which can be invisible if it has no other
use. Then the queries can use:
WHERE PERSONNEL.OFFICE=Forms!Variables.txtOFFICE

If you are opening recordsets for the queries, then you can
use code to plug in the values. First, I want to change the
names of your variables so they are not the same as the
field names, instead of just OFFICE, let's use varOFFICE:
WHERE PERSONNEL.OFFICE="varOFFICE'

The code to plug in the values would be like:
strSQL = Replace(rst.QryText, "prmOffice", varOffice)
Set rs = db.OpenRecordset(strSQL)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HELP --- Please 1
Dcount to create a new record in another table base on a record cr 0
Help needed passing data from the form to a table. 0
Referencing a table from VBA 1
SQL code to insert data from one table into another 5
Run time error 2467 - Using recordset to populate rowsources 0
Creating Hyperlinks from Fields within a Table 1
Referencing a field from a table 5

Top