Problem writing to 2nd table w/ 1st table's key

[Stevelx via AccessMonster.com]

Hi everyone. I'm as new to VBA as you can get. Please bear this in mind when
viewing my code. I'm self teaching myself and its not going so well. Anyhow,
I was hoping that someone could explain what I'm doing wrong. I'm working on
a small checklist program that were using out in the field to perform safety
inspections. I set my database up similar to an ordering system in that I
have a table named Inspections which I'm storing the building and owner data
& another table named DeficiencyDetail where I'm attempting to store each
separate deficiency. An inspection could have anywhere from zero to many
deficiencies, so I treated it like an order which could have multiple items.
Here's my code which writes the building info to the Inspections table:

MySQL = "INSERT INTO Inspections (INSPECTION_DATE, INSPECTOR_ID,
FACILITY_NAME, etc)"
MySQL = MySQL & " VALUES (InspectionDateTxt.Value, InspectorTxt.Value, etc)"

DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True

So far everything works fine. Where I having a problem is in the follow lines
of code. I'm trying to write out the detail lines to the DeficiencyDetail
table.


dim MySQL as String

If CheckMarkImg.Visible = True Then
MySQL = "INSERT INTO DeficiencyDetail (INSPECTION_REF_NUMBER,
DEFICIENCY_NUMBER, CODE_SITE_REF_NUMBER )"
MySQL = MySQL & " VALUES (INSPECTION_ID_NUMBER, '1a', 'RILSC 26.1.1.1.1')"

DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True

End If

My problem is that INSPECTION_ID_NUMBER is coming from the Inspections table
which I write to first. When I perform the write to the detail table, it's
capturing the INSPECTION_ID_NUMBER of the first record, not the last one. I
have setup a one to many relation, enforcing update ref integrity, between
the INSPECTION_ID_NUMBER on the Inspections table and INSPECTION_REF_NUMBER
on the DeficiencyDetail table. Also, the INSPECTION_ID_NUMBER is my primary
key on the Inspections table. My key on the DeficiencyDetail table is a
generic autonumber field that I'm not using at this moment.
I would appreciate any input anyone could provide.
TIA

 

[Douglas J. Steele]

If you're trying to use the value of a variable, you need to put the
variable outside of the quotes:

MySQL = "INSERT INTO DeficiencyDetail (INSPECTION_REF_NUMBER,
DEFICIENCY_NUMBER, CODE_SITE_REF_NUMBER )"
MySQL = MySQL & " VALUES (" & INSPECTION_ID_NUMBER & ", '1a', 'RILSC
26.1.1.1.1')"

That assumes INSPECTION_REF_NUMBER is a numeric field. If it's text, you
need to enclose the value in quotes:


MySQL = "INSERT INTO DeficiencyDetail (INSPECTION_REF_NUMBER,
DEFICIENCY_NUMBER, CODE_SITE_REF_NUMBER )"
MySQL = MySQL & " VALUES ('" & INSPECTION_ID_NUMBER & "', '1a', 'RILSC
26.1.1.1.1')"

 

[Stevelx via AccessMonster.com]

Douglas,
Thanks for the reply, but it's still grabbing the first record in my Detail
table. How do I get it to store the current INSPECTION_ID_NUMBER in my detail
table?
Right now if I were to create a new inspection, the next INSPECTION_ID_NUMBER
would be 46. Instead of '46' being the value stored in the detail record,
it's storing 37 which happens to be the first INSPECTION_ID_NUMBER in the
inspections table. It seems no matter what I try, I can't get VBA to look at
the current record instead of the first. Very frustrating.....
Thanks again for your help.

If you're trying to use the value of a variable, you need to put the
variable outside of the quotes:

MySQL = "INSERT INTO DeficiencyDetail (INSPECTION_REF_NUMBER,
DEFICIENCY_NUMBER, CODE_SITE_REF_NUMBER )"
MySQL = MySQL & " VALUES (" & INSPECTION_ID_NUMBER & ", '1a', 'RILSC
26.1.1.1.1')"

That assumes INSPECTION_REF_NUMBER is a numeric field. If it's text, you
need to enclose the value in quotes:

MySQL = "INSERT INTO DeficiencyDetail (INSPECTION_REF_NUMBER,
DEFICIENCY_NUMBER, CODE_SITE_REF_NUMBER )"
MySQL = MySQL & " VALUES ('" & INSPECTION_ID_NUMBER & "', '1a', 'RILSC
26.1.1.1.1')"

Hi everyone. I'm as new to VBA as you can get. Please bear this in mind
when

[quoted text clipped - 54 lines]

I would appreciate any input anyone could provide.
TIA

 

[John W. Vinson]

Hi everyone. I'm as new to VBA as you can get. Please bear this in mind when
viewing my code. I'm self teaching myself and its not going so well. Anyhow,
I was hoping that someone could explain what I'm doing wrong. I'm working on
a small checklist program that were using out in the field to perform safety
inspections. I set my database up similar to an ordering system in that I
have a table named Inspections which I'm storing the building and owner data
& another table named DeficiencyDetail where I'm attempting to store each
separate deficiency. An inspection could have anywhere from zero to many
deficiencies, so I treated it like an order which could have multiple items.
Here's my code which writes the building info to the Inspections table:

Um?

Why would you want to use *any code at all*?

It sounds to me like you could have a Form based on the Inspections table
(probably with a combo box to navigate to a specific site or inspection date),
with a Subform based on DeficiencyDetail; use the Inspection ID as the
master/child link field, and let Access take care of keeping them in synch.

Do you have some other reason for doing in code what can be done without any?