Automatically replace table used in query

[Hidge]

I have a database that tracks mileage driven in each State. Each piece
of equipment is in its own table. The reason for this is that the user
inputs the date, state & exiting odometer reading into a form and the
source query for the form calculates the distance based on the previous
record's odometer reading. I tried this with one table containing all
of the equipment, but my mileage did not calculate correctly. IF THERE
IS A BETTER WAY TO DO THAT I'M OPEN TO SUGGESTION - BUT THIS IS NOT MY
MAIN QUESTION.
I am trying to automate the addition of new equipment using a form and
macro that copies a master equipment table "~EQ" and saves the table
based on the value entered in the new equipment form. Then it copies
the master query "~qry_EQ" used for the mileage input form. In other
words, for equipment #123 the macro will create a table "123" and a
query "qry_123" that should use table "123." I have gotten as far as
creating the new query & opening it in SQL View & I want to find all
instances of ~EQ in the table & replace it with "123" (or the value
entered in the EQID field of the new equipment form). I am stuck.
How can I replace the table in the query? Or - how can the person who
will be inputting that data & adding new equipment do this with no
knowledge of Access 2003?
I've tried building a Replace expression - no luck -missing operands or
operators, etc.
Thanks in advance for any help.

 

[John Vinson]

I have a database that tracks mileage driven in each State. Each piece
of equipment is in its own table.

Then your database design is incorrect. Storing data - equipment names
- in a tablename is simply INCORRECT DESIGN.

The reason for this is that the user
inputs the date, state & exiting odometer reading into a form and the
source query for the form calculates the distance based on the previous
record's odometer reading. I tried this with one table containing all
of the equipment, but my mileage did not calculate correctly. IF THERE
IS A BETTER WAY TO DO THAT I'M OPEN TO SUGGESTION - BUT THIS IS NOT MY
MAIN QUESTION.

Fix it. The single table design with a field for the equipment ID is
MUCH simpler.

I am trying to automate the addition of new equipment using a form and
macro that copies a master equipment table "~EQ" and saves the table
based on the value entered in the new equipment form. Then it copies
the master query "~qry_EQ" used for the mileage input form. In other
words, for equipment #123 the macro will create a table "123" and a
query "qry_123" that should use table "123." I have gotten as far as
creating the new query & opening it in SQL View & I want to find all
instances of ~EQ in the table & replace it with "123" (or the value
entered in the EQID field of the new equipment form). I am stuck.
How can I replace the table in the query? Or - how can the person who
will be inputting that data & adding new equipment do this with no
knowledge of Access 2003?

You can't. Queries do not accept parameters to refer to tablenames.

I've tried building a Replace expression - no luck -missing operands or
operators, etc.
Thanks in advance for any help.

If you could explain what problem you were having looking up the
previous odometer reading for the piece of equipment in the one-table
approach, I'd really recommend going back to a correct table design
and fixing the query.

John W. Vinson[MVP]

 

[Hidge]

YES! One table will make setting equipment up on the fly much easier
for the user.
My equipment tables (plural at the moment) contain autonumbers & the
code I'm using to determine the previous odometer reading is based on
the value in the autonumber field. With all of the equipment in one
table I have a problem when the records change equipment numbers -
here's the code:

'*************************************************************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Function PrevRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim rs As Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set rs = F.RecordsetClone

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
rs.MovePrevious

' Return the result.
PrevRecVal = rs(FieldNameToGet)

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

'*************************************************************
' FUNCTION: NextRecVal()
' PURPOSE: Retrieve a value from a field in the next form
' record.
'**************************************************************
Function NextRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim rs As Recordset

On Error GoTo Err_NextRecVal

' The default value is zero.
NextRecVal = 0

' Get the form recordset.
Set rs = F.RecordsetClone

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the next record.
rs.MoveNext

' Return the result.
NextRecVal = rs(FieldNameToGet)

Bye_NextRecVal:
Exit Function
Err_NextRecVal:
Resume Bye_NextRecVal
End Function

**************************************************************************************
With all equipment in one table - the first record entered for a piece
of equipment looks at the previous record but the previous record
contains data for the previous piece of equipment - so the mileage
calulation is incorrect. If there is any code or an expression that
can reset the number when the equipment ID changes that would be sweet
- it doesn't HAVE to be an autonumber. I've played around with
counting records & such but haven't had much luck - I do not have allot
of experience in Access though. The data is entered on a date basis,
but there is no guaruntee that the user will enter the data in
consecutive date order - so I don't want to rely on the date field.
Thanks!!!!!!!

 

[John Vinson]

YES! One table will make setting equipment up on the fly much easier
for the user.
My equipment tables (plural at the moment) contain autonumbers & the
code I'm using to determine the previous odometer reading is based on
the value in the autonumber field. With all of the equipment in one
table I have a problem when the records change equipment numbers -
here's the code:

Well, you need at least two tables - a table of Equipment and a
related table of odometer readings. How are your TABLES structured? Is
the ID field the ID of a particular piece of equipment, or the ID of a
record in the odometer-readings table (the first option is what's
needed)?

John W. Vinson[MVP]

 

[Hidge]

As it stands now - this is the (~EQ) table structure:
ID (Autonumber)
Equipment ID (Text) <<< has a default value - intercompany equipment ID
Company (Text) <<<has a default value - we have multiple companies
Date (Date/Time)
State (Text)<<< Row Source - "SELECT tbl_States.Abbreviation FROM
tbl_States; "
Odometer (Number)
Gallons (Number)

59,999 (Yes/No)<<<there is a different tax rate for vehicles over 59,999 lbs - this is also a default value

All of the fields from the table are used in a query - the query is
used the record source for the mileage/gas input form. Sorry if this
is overkill but here is the query:
SELECT [~EQ].ID, [~EQ].[Equipment ID], [~EQ].Company, [~EQ].Date,
[~EQ].State, [~EQ].Odometer, [~EQ].Gallons, [~EQ].[>59,999],
DLookUp("[Odometer]","~EQ","[ID] = " & [ID]+1) AS [Previous Odometer],
[Previous Odometer]-[Odometer] AS [Miles Driven], [Gas Miles]/[Gallons]
AS MPG, DLookUp("[Odometer]","~EQ","[ID] = " & [ID]-1) AS [Next
Odometer], [Odometer]-[Next Odometer] AS [Gas Miles]
FROM [~EQ];

The [Previous Odometer] and [Next Odometer] come from the code in my
earlier reply.

Should the "ID" autonumber be in the odometer-reading table? I
consider it more of a transaction number than an equipment identifier -
ID was just short & easy to use.

 

[Hidge]

If you are still considering this question - I just realized that I am
NOT using the code that I included in my first post. I have combined
all equipment into one table & have all the odometer & gas data in
another table. Trying to figure out how to count the records for a
given piece of equipment & use that count in the Previous & Next
Odometer reading expressions. It is VERY cranky.
Thanks!