How can I use public variable between 2 forms 3

[jujutsu101]

I have a 1 (Billing Address) to many (Site Addresses) relationship tables. This is a small carpet cleaning database program. I have 2 linked forms. The first for billing. The second for sites. Sometimes the billing address is the same for the site that the company is cleaning. In thoses situations, the owner wants to be able (on the site form) to click a button and duplicate the Billing Address into the Site Address field. I realize it can be done. I think a Public Variable is useful. But after hours of frustration, I turned to someone(s) out there in Access/VBA land to help me please. Thanks in advance! Fred (jujutsu101@hotmail.com)

 

[jebry]

Hi Fred!

Try this code in the button's click event:

Dim rst As DAO.Recordset
Dim sql As String

sql = "Select * From YourBillingTable Where IDField = '" & Me!YourForeignKeyTextBox & "'"

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

If rst.EOF = True And rst.BOF = True Then
Call MsgBox("There are no records available&quot
Else
With rst
YourAddressTextBox = !AddressField
YourCityTextBox = !CityField
etc.
End With
End If

Set rst = Nothing

Since you said you had set up a one to many relationship I assumed that the billing table had a primary key and you had stored that informataion in the site table and that the ID information would be available before the button was clicked.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[bdfigler]

It can be done, but I don't think a Public Variable is the way to go. You'll want to trigger an event procedure when the field is checked and in the event procedure you can open up any data you want.

The easiest way to do it is to use a query as the form's recordset instead of a table. Just include the fields you need from the billing address table in the query (along with all the other fields you need for the form) and you can access these fields from the form just like any other.

Another way is to open a recordset in VBA.

Either way, you'll want to "manually" place the address in the appropriate fields. It would go like this:

Private Sub CheckToInsertBillingAddress_OnClick()
Dim objRS As Recordset, strSQL As String
strSQL = "SELECT * FROM [billing address] WHERE [clientname] = '" & Me.inptClientName & "'"
Set objRS = CurrentDb.OpenRecordset(strSQL)
Me.inptAddress1 = objRS("strAddress1&quot
Me.inptCity = objRS("strCity&quot
'etc...
Set objRS = Nothing
End Sub

You could do away with the first three lines and the last if you use a query as the recordset. These fields would already be in the current recordset and you wouldn't need to make a new one.

 

[Philly44]

I could be wrong but I think it may be easier than people are reding into it. I've had similar situations and I solved them as follows(as long as you just want the address info to copy over).

add a button to the site form to do this. In its OnClick event put the following(changing the control names to what you used)

Me.AddressText.SetFocus
Me.AddressText.value = Forms!BillingForm!ItsAddressEditBox.Value

I think that should accomplish basically what your explaing, if the address is stored in several fields(which really it should be) just do the same for each Edit box making sure to give it the focus before you copy the data


I hopes this helps

 

[bdfigler]

Why do you need to move the focus to a control before you assign it a value?