Append query form + subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ami - 30 Jul 2006 21:33 GMT

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

Reply to this Message

tina - 31 Jul 2006 00:47 GMT

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

> Hi everybody,
>
[quoted text clipped - 67 lines]
>
> Erika

Reply to this Message

Ami - 02 Aug 2006 21:08 GMT

> 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
>
[quoted text clipped - 3 lines]
> "FROM [Tbl_Audio_PRINCIPALETRACK] WHERE Univoco = "
> & Me.Univoco & ";"

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

> > Hi everybody,
> >
[quoted text clipped - 67 lines]
> >
> > Erika

Reply to this Message

tina - 03 Aug 2006 14:09 GMT

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

> I am afraid that, somehow, here is the problem:
>
[quoted text clipped - 3 lines]
> so that they may have now the same value as the corresponding field in
> the main form.

Reply to this Message