Opening Form2 with Data related to Form1?

mbrost

Registered User.
Local time
Today, 11:11
Joined
Jun 27, 2005
Messages
26
Hi,

I've searched but cant find the answer,

I have an Account form (Form1) and it displays account information along with their many addresses for the account (in a datagrid embedded form). I hit "update or add address" button to launch form2 (Address Form). From this point I want to query the Address and AddressAccountLink table with a passed in Global Variable (AccountID) from Form 1 so that just the Addresses pertaining to that Account show up, instead of all the Addresses for all accounts.

How do I do this? I've tried a String Query in the Private Sub Form_Load() but it gives me errors, here that is:
sSQLQueryAddr = "SELECT tblLinkAccAdd.AccountID, tblLinkAccAdd.AddressID, tblAddress.* FROM tblAddress INNER JOIN tblLinkAccAdd ON tblAddress.AddressID = tblLinkAccAdd.AddressID WHERE (((tblLinkAccAdd.AccountID)=" & v_accountid & "))" (it's on one line in the code)
followed by:

DoCmd.SetWarnings False
DoCmd.RunSQL sSQLQueryAddr
DoCmd.SetWarnings True

Any ideas? Am I on the right track? Do I move this query elsewhere? The address form is made from a query -- querying the Address and address/account link table.

Oh, the v_accountid does have the correct value passed to it...

Please help , I'm going nuts here!! :confused:

Mike
 
Last edited:
Create a query that has the fields you want in the address form. In the criteria for the AccountID column use:

=Forms!form1!txtAccountID or whatever names you are using

then set that query as the Recordsource for the form.
 
Thanks Scott,

But now when I open form2, the form is blank -- i'm assuming the query on form2 cant see the value for =Forms!form1!txtAccountID

What is the syntax for me to msgbox the value of "=Forms!form1!txtAccountID " to find out if it is indeed null?

I should note that I have the AccountID value stored in a variable when this form opens, as I use it to link the account in form1 to the address in form2. can i use this variable in my WHERE clause in my query somehow?

Thanks for any help!

Mike
 
Are you closing Fomr1? It needs to be open for the value to be read.
 
Form1 stays open, I just dont know the syntax to put the variable name in the WHERE clause of the query, if that is indeed how I'd do it. Form1 is based on a table, and Form2 uses a query, if that changes things.

(I'm new to Access and VB, so forgive my ignorance :)
 
Remove all the code you've got right now in your form2 to filter your data.
Now see to it that you add a bound control box in form2, based on AccountID in tblLinkAccAdd.
Set the default value for this control box to the AccountID controlbox in your form1.
Optionally, if you don't want to show the control box , set it's property Visible to No.
And off you go.

RV
 
Just use the Where clause of the OpenForm method, the wizard will create the code for you
 
I use the method I suggested all the time with no problem. If you form is blank, then either you have Data Entry set to yes, are opening the form in Add mode, the txtAccountID value doesn't match any record so the query returns nothing or you aren't using the correct names or controls.
 
I've tried all these solutions with no luck :(

I also cant find this OpenForm wizard, when I add anything to the Events it makes me manually type the VB code.

I have attached my DB file, if someone had time to take a quick peek -- its a very simple app. The frmAccount has an Update Address button that calls frmAddressAccount -- I can't believe something so simple is giving me this much grief, can someone help a noobie out?? :eek:

Greatly appreciated!

Mike
 

Attachments

I also cant find this OpenForm wizard

Open your form in design mode.
Add a button to the form, this will start the Command Button wizard.
Now for Category choose Form Operations, for Action choose Open Form.
Choose the form you'd like to open, next opt for "Open the form and find specific data to display".
Now it's merely a matter of specifying the data....

RV
 
mbrost said:
I've tried all these solutions with no luck :(

I also cant find this OpenForm wizard, when I add anything to the Events it makes me manually type the VB code.

I have attached my DB file, if someone had time to take a quick peek -- its a very simple app. The frmAccount has an Update Address button that calls frmAddressAccount -- I can't believe something so simple is giving me this much grief, can someone help a noobie out?? :eek:

Greatly appreciated!

Mike


Ahh I see the problem. Because you have setup a many to many relation between account and address this complicates things. You need to do things a bit differently. You need to base the frmaddress Account just on the tblAddress. This will open up Address as a blank form. You enter the address info. Then in the On Close event you need to add a record to tblLinkAdd. it would look like this:

Code:
Dim strSQL As String

strSQL = "INSERT INTO tblLinkAdd (AccountID, AddressID) "
strSQL = strSQL & "VALUES(" &  [Forms]![frmAccount]![AccountID] & ", " 
strSQL = strSQL & Me!AddressID & ");"
CurrentDB.EXecute strSQL

Forms!frmAccount!frmAccountAddresses.Requery

This should allow you to add an address, associate it with the currently displayed account and display the newly added address in the subform.
 
RV said:
Open your form in design mode.
Add a button to the form, this will start the Command Button wizard.
Now for Category choose Form Operations, for Action choose Open Form.
Choose the form you'd like to open, next opt for "Open the form and find specific data to display".
Now it's merely a matter of specifying the data....

RV

This worked for me :)

Thanks RV, Scott and others for taking the time to lend a hand to a useless newbie!

Mike
 
mbrost said:
This worked for me :)

Thanks RV, Scott and others for taking the time to lend a hand to a useless newbie!

Mike

MB,
I'd strongly suggest you look more carefully at my solution. First it doesn't require any further action of selecting a specific record. Second, it eliminate the need for basing your form on a multiple table query, which is not the most advisable thing to do.

Again, your addresses are SEPARATE from the Accounts. They are linked thru the Join table. Therefore you don't need to know what account its for when you enter the address. Its only AFTER entering that you need to add a record to your join table, linking the address and the account. My suggestion does that automatically.
 

Users who are viewing this thread

Back
Top Bottom