Appending question

[vbnetman via AccessMonster.com]

Good morning,

I will start with an overview of my plan. I have 3 tables arranged as follows:
(may need to sketch this one)

Table1:
UnitID – PK
MeterID – PK
(plus other fields)


Table2:
UnitID – PK (FK to Table1, UnitID)
MeterID – PK (FK to Table1, MeterID)
ReplacementNo – PK
(plus other fields)

Table3:
OrderID – PK – autonumber
UnitID – PK (FK to Table2, UnitID
MeterID – PK (FK to Table2, MeterID)
ReplacementNo – PK (FK to Table2, ReplacementNo)
(plus other fields)

The arrangement is used to track various meters on equipment. A unit can have
several meters like hourmeter, odometer etc. Thus, unit1 can have meter1,
meter2 and so on. Over time, meter1 may fail and need to be replaced. It is
replaced by another ‘meter1’ but is flagged as a replacement, allowing it to
remain as ‘meter1’. Table1 is used as a historical table, storing a
cumulative total for a particular unit and its associated meter. Table 2
stores info specific to each meter like whether its been replaced and the
maximum reading like 9999. Finally, Table3 stores meter readings and date
read among other thing. I know this may be exhaustive. Here’s my dilemma.
After Table 1 is populated with data at the form level, I want to run 2
append queries that copy UnitID and MeterID from Table 1 to Table 2 and Table
3 simultaneously. I have been successful with this with the following code
however I need to run it twice. The first run populates Table 2 – the second
run populates Table 3. Not sure why but I think that the record is not
actually saved to the table until I move off of the record. Then, I can go
back and do it again. I would like to accomplish this in one pass. I’m
thinking there is code that that can be added to save the record, allowing
this process to occur. Does anyone have any thoughts or different approach
suggestions?

Thank you

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stdocnames As String

stDocName = "query3"
stdocnames = "query4"

DoCmd.OpenQuery stdocnames, acNormal, acAdd
DoCmd.OpenQuery stDocName, acNormal, acAdd

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.description
Resume Exit_Command4_Click

End Sub

 

[KARL DEWEY]

A couple of things --
You can only have one primary key in a table or does your 'PK' mean
something else?
Why do you repeat the exact same fields in following table?

 

[vbnetman via AccessMonster.com]

Hi Karl,
Thank you for the response. In this scenario, multiple primary keys (PK’s)
are necessary. In order to have, for example, Unit 1 with 3 different meters,
duplicates would occur without it. As far as having the same fields, they
would need to be named the same in order for the append to function correctly.
Yes, it appears that there is duplicate data but one of the tables is serving
as a historical record with records that may be purged off at a later date.

A couple of things --
You can only have one primary key in a table or does your 'PK' mean
something else?
Why do you repeat the exact same fields in following table?

Good morning,

[quoted text clipped - 61 lines]

End Sub

 

[KARL DEWEY]

If I understand correctly you do not have multiple primary keys (PK’s) but
have a primary key that consist of multiple fields.
I would suggest that Table1 have an autonumber primary key and then a
multiple field index set to unique using UnitID and MeterID. That primary
key can then be used in any table to identify that particular unit/meter.

Hi Karl,
Thank you for the response. In this scenario, multiple primary keys (PK’s)
are necessary. In order to have, for example, Unit 1 with 3 different meters,
duplicates would occur without it. As far as having the same fields, they
would need to be named the same in order for the append to function correctly.
Yes, it appears that there is duplicate data but one of the tables is serving
as a historical record with records that may be purged off at a later date.

A couple of things --
You can only have one primary key in a table or does your 'PK' mean
something else?
Why do you repeat the exact same fields in following table?

Good morning,

[quoted text clipped - 61 lines]

End Sub

 

[vbnetman via AccessMonster.com]

This would be correct

<<<...but
have a primary key that consist of multiple fields.>>>

With respect to populating the other tables, is it possible to use code to
save the record after its written but before the next one is (see below) or
am I off track?

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stdocnames As String

stDocName = "query3"
stdocnames = "query4"

DoCmd.OpenQuery stdocnames, acNormal, acAdd

<<<<<Some coding here to save the record>>>>>>>
then.....
DoCmd.OpenQuery stDocName, acNormal, acAdd

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.description
Resume Exit_Command4_Click

End Sub

If I understand correctly you do not have multiple primary keys (PK’s) but
have a primary key that consist of multiple fields.
I would suggest that Table1 have an autonumber primary key and then a
multiple field index set to unique using UnitID and MeterID. That primary
key can then be used in any table to identify that particular unit/meter.

Hi Karl,
Thank you for the response. In this scenario, multiple primary keys (PK’s)

[quoted text clipped - 14 lines]

 

[KARL DEWEY]

I can not answer your question about use of code. I interface with my tables
using a query as record source for a form. When I create a new record in the
form it is automatically saved and any data in a field is save when I exit
that field.

This would be correct

<<<...but
have a primary key that consist of multiple fields.>>>

With respect to populating the other tables, is it possible to use code to
save the record after its written but before the next one is (see below) or
am I off track?

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stdocnames As String

stDocName = "query3"
stdocnames = "query4"

DoCmd.OpenQuery stdocnames, acNormal, acAdd

<<<<<Some coding here to save the record>>>>>>>
then.....
DoCmd.OpenQuery stDocName, acNormal, acAdd

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.description
Resume Exit_Command4_Click

End Sub

If I understand correctly you do not have multiple primary keys (PK’s) but
have a primary key that consist of multiple fields.
I would suggest that Table1 have an autonumber primary key and then a
multiple field index set to unique using UnitID and MeterID. That primary
key can then be used in any table to identify that particular unit/meter.

Hi Karl,
Thank you for the response. In this scenario, multiple primary keys (PK’s)

[quoted text clipped - 14 lines]

End Sub

 

[vbnetman via AccessMonster.com]

Karl,
Thank you for your time and thought. I believe I have a resolution. As I
noted, I'm trying to run 2 queries, possibly in code as shown. However, a
workaround is to run 1 query via a "Save" command button on a form and run
the second query on a "Close" command button. Obviously this generates more
issues but that's what programming's about. Again, thanks for your time!

This would be correct

<<<...but
have a primary key that consist of multiple fields.>>>

With respect to populating the other tables, is it possible to use code to
save the record after its written but before the next one is (see below) or
am I off track?

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stdocnames As String

stDocName = "query3"
stdocnames = "query4"

DoCmd.OpenQuery stdocnames, acNormal, acAdd

<<<<<Some coding here to save the record>>>>>>>
then.....
DoCmd.OpenQuery stDocName, acNormal, acAdd

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.description
Resume Exit_Command4_Click

End Sub

If I understand correctly you do not have multiple primary keys (PK’s) but
have a primary key that consist of multiple fields.

[quoted text clipped - 7 lines]