NESTED SUBFORMS, QUERIES

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ash - 29 Oct 2007 22:57 GMT

I have a 3 tables,

table 1 table 2 table 3
ID ID J_ID
Part Number Part Number NHA_ID
J_ID NHA

on a form, i have table 3 a subform in table 2, and table 2 a subform in
table 1. There is a form that pops up when the user wants to duplicate the
record, and the user can change some data on that form. I got it to
duplicate the data in table 1 and table 2, but am stuck on duplicating the
data in table 3 correctly.

this is the sql data for appending data to table2
If Me.table2_subform.Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO table2(ID, part_number, " & _
"SELECT " & lngMPNID & " As ID, " & _
"table2.part_number " & _
"FROM table2 " & _
"WHERE (table2.ID = " & Me.ID & ");"
db.Execute strSQL1, dbFailOnError
End If

this is the sql statement for table 3
If me.table2_subform.Form.table3_subform.Form.RecordsetClone.RecordCount > 0
Then
strSQL = "INSERT INTO table3 (J_ID, NHA) " & _
"SELECT " & LngJID & "AS J_ID, table3.NHA" & _
"FROM (table1 INNER JOIN table2 ON "(table1.PART_NUMBER
= table2.PART_NUMBER) AND (table1.ID = table2.ID)) " & _
"INNER JOIN table3 ON table2.J_ID = table3.J_ID " & _
"WHERE (table2.ID = ME.ID " & ");"
db.Execute strSQL, dbFailOnError
End If

Any help on fixing this sql statement would be appreciated. Thanks.
ash

Reply to this Message

John Spencer - 30 Oct 2007 16:52 GMT

Watch out for missing spaces. I usually format my query like the following
and always include a space immediately after the first quotation mark.
Extra spaces don't hurt, missing spaces do.

You also need to concatenate in the value of Me.Id (assumption it is a
number) as the query engine won't have any idea of what ME.ID is.

strSQL = "INSERT INTO table3 (J_ID, NHA) " & _
" SELECT " & LngJID & "AS J_ID, table3.NHA" & _
" FROM (table1 INNER JOIN table2 " & _
" ON Table1.PART_NUMBER = table2.PART_NUMBER" & _
" AND table1.ID = table2.ID) " & _
" INNER JOIN table3 ON table2.J_ID = table3.J_ID " & _
" WHERE (table2.ID = " & ME.ID & ");"

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a 3 tables,
>
[quoted text clipped - 35 lines]
> Any help on fixing this sql statement would be appreciated. Thanks.
> ash

Reply to this Message

ash - 30 Oct 2007 17:24 GMT

is there anyway to find out what the new J_ID (autonumber in table 2) is, so
that I can append it to table 3? Right now the sql works, but it appends the
data to the old J_ID instead of the new one.

> Watch out for missing spaces. I usually format my query like the following
> and always include a space immediately after the first quotation mark.
[quoted text clipped - 50 lines]
> > Any help on fixing this sql statement would be appreciated. Thanks.
> > ash

Reply to this Message

Hansen - 30 Oct 2007 19:48 GMT

Hi Ash

Take a look at the .Bookmark statement. Set the value of a textbox or other
declaration to the J_ID and then just update the field in table 3 with that
value.

>is there anyway to find out what the new J_ID (autonumber in table 2) is, so
>that I can append it to table 3? Right now the sql works, but it appends the
[quoted text clipped - 5 lines]
>> > Any help on fixing this sql statement would be appreciated. Thanks.
>> > ash

Reply to this Message

ash - 30 Oct 2007 21:01 GMT

How would i bookmark the subform?

> Hi Ash
>
[quoted text clipped - 11 lines]
> >> > Any help on fixing this sql statement would be appreciated. Thanks.
> >> > ash

Reply to this Message

Hansen - 31 Oct 2007 07:57 GMT

Hi Ash

Instead of using dbs.execute you can do something like this:

Dim Dbs as Database
Dim Rst as Recordset
Dim Qry as String
Dim varBookM as Variant

Set Dbs = Currentdb()
Qry = "table2"
Set Rst = Dbs.OpenRecordset(Qry, dbOpenDynaset)
With Rst
.addnew
!dbfield1 = valuex
!dbfield2 = valuey
.update
varBookM = .Bookmark
.close
End With

Qry = "table3"
Set Rst = Dbs.OpenRecordset(Qry, dbOpenDynaset)
With Rst
.addnew
!dbfield1 = varBookM
!dbfield2 = ...etc.
.update
.close
End With

>How would i bookmark the subform?
>
[quoted text clipped - 3 lines]
>> >> > Any help on fixing this sql statement would be appreciated. Thanks.
>> >> > ash

Reply to this Message

Hansen - 31 Oct 2007 08:12 GMT

My Apologies Ash!

varBookM = .Bookmark ----> should be replaced with the following two
lines:

.bookmark = .lastmodified
varBookM = !J_ID

>Hi Ash
>
[quoted text clipped - 32 lines]
>>> >> > Any help on fixing this sql statement would be appreciated. Thanks.
>>> >> > ash

Reply to this Message

ash - 31 Oct 2007 17:53 GMT

thanks for your help hansen. This only duplicates table 2 once.
The thing is Table 2 is a subform and can many records that are linked to
table 1. Table 3 is a subform of Table 2 and for each record in table 2,
there can be many records in table 3.
I tried using a do loop but it still would only capture one record in table
2 when there is suppose to be 2 record.

This is what I have, please let me know what's wrong with it.

duplicating table 1 form (this works fine)
With Me.RecordsetClone
.AddNew
!MAIN_PART_NUMBER = Me.MAIN_PART_NUMBER
!REVISION = Me.REVISION
.Update
.Bookmark = .LastModified
lngMPNID = !ID

duplicating multiple records in table 2 (not working- somehow not picking up
the second record)
Dim rst As Recordset
Set rst = Me.table2.Form.RecordsetClone
With Me.J_NUMBER_subform.Form.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
.AddNew
!ID = lngMPNID
!J_NUMBER = Me.table2.Form.J_NUMBER
!VARIANT = Me.table2.Form.VARIANT
.Update
.Bookmark = .LastModified
NEWJ_ID = !J_ID

duplicating multiple records in table 3 for each record in table 2
(works)
If Me.table 2.Form.table3.Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO table3 (J_ID, NEXT_HIGHER_ASSEMBLY ) "
& _
"SELECT " & NEWJ_ID & " as J_ID, table3.NEXT_HIGHER_ASSEMBLY
" & _
"FROM table3" & _
"WHERE (table2.J_ID = " & Me.table2.Form.J_ID & ");"
db.Execute strSQL, dbFailOnError
End If

.MoveNext
Loop
End If
End With
Set rst = Nothing

Reply to this Message