Append query form + subform

  • Thread starter Thread starter Ami
  • Start date Start date
A

Ami

Hi everybody,

I am using the query example by Mr. Browne to duplicate a record in
form and subform.

This is for a database storing data about music cd for a small library.
They don't have money to buy a problem, but would like to have a
versatile system.
I am not very good at this, but I did not want to say no to them.

This is the code I am using:

------------
Private Sub CopiaIncolla_Click()
'Purpose: Duplicate the main form record and related records in the
subform.
[CUT]

strValue1 = InputBox("Inserisci il Nuovo CatalogueNr")
strValue2 = InputBox("Inserisci il Nuovo Univoco")

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!CatalogueNr = strValue1
!Univoco = strValue2
!Title = Me.Title
!Label = Me.Casa_discografica
'etc for other fields.
.Update

-----------------------------------
This part is working fine.

BUT
THIS IS WHERE I AM STUCK

'Duplicate the related records: append query.
If
Me.[Sfr_Audio_PrincipaleTrack].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Tbl_Audio_PRINCIPALETRACK]
(CatalogueNr, Univoco, TrackNr, Title, Length, Autore, Cantante, Strum,
Esecutore ) " & _
"SELECT '" & strValue1 & "' As CatalogueNr, '" &
strValue2 & "' as Univoco, TrackNr, Title, Length, Autore, Cantante,
Strum, Esecutore" & _
"FROM [Tbl_Audio_PRINCIPALETRACK] WHERE Univoco = "
& Me.Univoco & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
-----------------------------------------------

The PrimaryKey is an autonumber

Then I have the following fields:
CatalogueNr
Univoco
TrackNr
Title
and so on...

Catalogue Nr and Univoco are text and the same values I need to use for
the first append, so strvalue1 e strvalue2.

But now I keep getting an error message (error 2465, field "|" not
found).

What am I doing wrong?

Thanks in advance for the help!

Erika
 
if you've copied/pasted the code directly from your VBA module (and ignoring
the automatic line wrap that occurs in the newsreader), then your problem
might be the lack of a space between Esecutore and FROM, as

"SELECT '" & strValue1 & "' As CatalogueNr, '" &
strValue2 & "' as Univoco, TrackNr, Title, Length, Autore, Cantante,
Strum, Esecutore" & _
"FROM [Tbl_Audio_PRINCIPALETRACK] WHERE Univoco = "
& Me.Univoco & ";"

so that, when concatenated by the system, the string reads as " as Univoco,
TrackNr, Title, Length, Autore, Cantante, Strum, EsecutoreFROM
[Tbl_Audio_PRINCIPALETRACK] WHERE Univoco = "

try adding a space after Esecutore, as

"SELECT '" & strValue1 & "' As CatalogueNr, '" &
strValue2 & "' as Univoco, TrackNr, Title, Length, Autore, Cantante,
Strum, Esecutore " & _
"FROM [Tbl_Audio_PRINCIPALETRACK] WHERE Univoco = "
& Me.Univoco & ";"

hth
 
tina said:
if you've copied/pasted the code directly from your VBA module (and ignoring
the automatic line wrap that occurs in the newsreader), then your problem
might be the lack of a space between Esecutore and FROM, as
[cut]

try adding a space after Esecutore, as

"SELECT '" & strValue1 & "' As CatalogueNr, '" &
strValue2 & "' as Univoco, TrackNr, Title, Length, Autore, Cantante,
Strum, Esecutore " & _
"FROM [Tbl_Audio_PRINCIPALETRACK] WHERE Univoco = "
& Me.Univoco & ";"
Click to expand...


Hi!
I've been out of town for a couple of days, but I am back.

Thanks for you suggestions, I followed your advice, bit it is not
working.

I am afraid that, somehow, here is the problem:

"SELECT '" & strValue1 & "' As CatalogueNr, '" & ...

I need to change the values of the two fields CatalogueNr and Univoco
so that they may have now the same value as the corresponding field in
the main form.

But I can't get it to work.

Thanks in advance!

Erika





Ami said:
Hi everybody,

I am using the query example by Mr. Browne to duplicate a record in
form and subform.

This is for a database storing data about music cd for a small library.
They don't have money to buy a problem, but would like to have a
versatile system.
I am not very good at this, but I did not want to say no to them.

This is the code I am using:

------------
Private Sub CopiaIncolla_Click()
'Purpose: Duplicate the main form record and related records in the
subform.
[CUT]

strValue1 = InputBox("Inserisci il Nuovo CatalogueNr")
strValue2 = InputBox("Inserisci il Nuovo Univoco")

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!CatalogueNr = strValue1
!Univoco = strValue2
!Title = Me.Title
!Label = Me.Casa_discografica
'etc for other fields.
.Update

-----------------------------------
This part is working fine.

BUT
THIS IS WHERE I AM STUCK

'Duplicate the related records: append query.
If
Me.[Sfr_Audio_PrincipaleTrack].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Tbl_Audio_PRINCIPALETRACK]
(CatalogueNr, Univoco, TrackNr, Title, Length, Autore, Cantante, Strum,
Esecutore ) " & _
"SELECT '" & strValue1 & "' As CatalogueNr, '" &
strValue2 & "' as Univoco, TrackNr, Title, Length, Autore, Cantante,
Strum, Esecutore" & _
"FROM [Tbl_Audio_PRINCIPALETRACK] WHERE Univoco = "
& Me.Univoco & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
-----------------------------------------------

The PrimaryKey is an autonumber

Then I have the following fields:
CatalogueNr
Univoco
TrackNr
Title
and so on...

Catalogue Nr and Univoco are text and the same values I need to use for
the first append, so strvalue1 e strvalue2.

But now I keep getting an error message (error 2465, field "|" not
found).

What am I doing wrong?

Thanks in advance for the help!

Erika
Click to expand...
Click to expand...
 
in the code you originally posted, the strValue1 and strValue2 values are
coming from input boxes. if you want the values to be taken from controls on
your form instead, then you need to change the values assignments for those
two variables, as

strValue1 = Me!CatalogueNr
strValue2 = Me!Univoco

if the two controls on your form have different names, then substitute the
correct names in the code above.

hth
 
Back
Top