Prev: mail merge in access 2007
Next: Re-Establishing Connection to Back-End
From: Ryker on 20 Jan 2010 16:50
I am trying to append the current record in a subform to another table
with no luck.

On the append query, I am appending part no, part type to another
table based on Line ID. On the Line ID criteria I have the following
code: [forms]![Mainform]![subform].Line ID. When running this, the
query cannot find Line ID...It will ask for it to be populated.

However, when I pull the subform up by itself and change the code on
the append query to
[forms]![subform].line id it will work fine. What am I doing wrong?

Thanks
From: Sanford on 20 Jan 2010 17:50
On Jan 20, 2:50 pm, Ryker <knight_1...(a)yahoo.com> wrote:
> I am trying to append the current record in a subform to another table
> with no luck.
>
> On the append query, I am appending part no, part type to another
> table based on Line ID.  On the Line ID criteria I have the following
> code:  [forms]![Mainform]![subform].Line ID.  When running this, the
> query cannot find Line ID...It will ask for it to be populated.
>
> However, when I pull the subform up by itself and change the code on
> the append query to
> [forms]![subform].line id it will work fine.  What am I doing wrong?
>
> Thanks

Hi Ryker- I find that calling form fields as query variables is
unreliable at best... so I have another way of doing it, which allows
you to keep your query defined (rather than creating it on the fly in
code).

I create a very simple module called "modVariables" as follows:

********************
Option Compare Database
Option Explicit

Public lngGL_TempID as Long

Public Function fcnGL_TempID as Long
fcnGL_TempID = lngGL_TempID
End Function
********************

Prior to executing the Append query, I set lngGL_TempID to the ID
contained in the form. In the query, I put fcnGL_TempID() where you
have [forms]![Mainform]![subform].Line ID.

Then I call the append query using CurrentDb.Execute
"qryMyAppendQuery", dbFailOnError

Note you can't just call the variable lngGL_TempID from a query; you
can only call functions from queries. Don't forget the () after the
function name.

Hope that helps!

 | 
Pages: 1
Prev: mail merge in access 2007
Next: Re-Establishing Connection to Back-End