Using AppendQuery Criteria and Forms

  • Thread starter Thread starter Brant
  • Start date Start date
B

Brant

Hi,
What I am trying to do is run an append query to add a row in a table
but I only want to append the row of the form I am in. For example, I
want to append Names into a different table but only when a button is
pressed on the form, and I only want that one value appended. How should
I setup my criteria in my append query to accomplish this?
Thanks in advance
 
Use DAO or ADO to add the record to the second table not an append query.
Alternately you can have a subform bound to the second table on your form
showing a new blank entry It doesn't have to be visible. Copy the values you
want to transfer into variables then have an action button <on click> set the
values on form#2 equal to the values you saved in the variables. Or you can
transfer the values directly as follows.

forms![form1]![subform2].form![Name] = Me.Name
 
tynerr said:
Use DAO or ADO to add the record to the second table not an append query.
Alternately you can have a subform bound to the second table on your form
showing a new blank entry It doesn't have to be visible. Copy the values you
want to transfer into variables then have an action button <on click> set the
values on form#2 equal to the values you saved in the variables. Or you can
transfer the values directly as follows.

forms![form1]![subform2].form![Name] = Me.Name
Click to expand...
If I wanted to add More than 1 name into the subform, ie create more
records in the subtable how would i go about doing that. Say in a
certain form, i wanted to automatically enter Bob and Alice.
thanks
 
Brant:

I suggested four ways to do it and, with your latest memo I advise method
four is the easiest to follow..

When you select a record in one form then it becomes the active record. I
think you probably have a datasheet view of one table and you want to select
a record there (on the datasheet) to add to the other table. You do this by
selecting the record you want to transfer then click a button to add to
table#2. Clicking the button transfers the values one at a time into the
appropriate fields on Form#2 for Table#2.

Assume you have twenty fields in Table#1 and 3 fields in Table#2
Form1 for table#1 and SubForm2 for Table#2

Table#1 has ....First_Name, Second_Name, StudentID, ... etc
Table#2 has only First_Name, Second_Name, StudentID.
The new Button is on Form#1 as MyButton

SubForm2 is <Data Entry> only. (Properties, Form, Data, Data Entry = Yes)
The highligthed record is on Form1 (Table#1 data)

You select the record to transfer then click the MyButton button.

Here is the code
Private Sub MyButton_Click()
forms![form1]![subform2].form![First_Name] = Me.First_Name
forms![form1]![subform2].form![Last_Name] = Me.Last_Name
forms![form1]![subform2].form![StudentID] = Me.StudentID
DoCmd.RunCommand Accommand.SaveRecord
' At this point the record will save and a new blank record will appear on
SubForm2
End sub

Next, select the next record to transfer, and click MyButton etc. This
process allows you to copy one record at a time from one table to the other.
As noted Earlier, SubForm2 does not have to be visible.

Hope this helps,

jim at andersonsoftware dot ca

Brant said:
tynerr said:
Use DAO or ADO to add the record to the second table not an append query.
Alternately you can have a subform bound to the second table on your form
showing a new blank entry It doesn't have to be visible. Copy the values you
want to transfer into variables then have an action button <on click> set the
values on form#2 equal to the values you saved in the variables. Or you can
transfer the values directly as follows.

forms![form1]![subform2].form![Name] = Me.Name
Click to expand...
If I wanted to add More than 1 name into the subform, ie create more
records in the subtable how would i go about doing that. Say in a
certain form, i wanted to automatically enter Bob and Alice.
thanks
Click to expand...
 
Brant said:
Hi,
What I am trying to do is run an append query to add a row in a table
but I only want to append the row of the form I am in. For example, I
want to append Names into a different table but only when a button is
pressed on the form, and I only want that one value appended. How should
I setup my criteria in my append query to accomplish this?
Thanks in advance
Click to expand...

Hi,
Answer your question
In append query, write the name of criteria field in criteria section
including reference of active form.
Example
your active form name is Form1 and criteria field is Name then write
following statement on criteria section of append query
[Form]![Name]
After save the query run the From1 and press the append query button it may
solved your problem.
 
Back
Top