Pass value to new form

[Guest_imported]

This novice needs some code help. I know that I need to execute an"on change event". One Table: Zipcode, fields: ZipI(Zipcode), City, StateCode. Second table (the form is based on): Broker, Many Fields including: Zipcode, City, State. I have an existing form for each that maintains the data.

The problem: When a user enters a zipcode in the Broker based form I want to change the value of CIty & State on the form (auto populate so they do not have to type the City or State code) by getting the corresponding value from the Zip Table. But if the zip is not found I want to open the Zip form (without closing the current form) that mantains the zip code table so they can enter the City and State (assuming second form can automatically open a new record with the entered zip that was not found in the main form) and then when that form closes populate the city and state automatically.

Both my existing forms for broker and zip code are based upon queries not directly tied to the underlying table.

Thanks for any help !!

 

[jebry]

Hi!

Actually I think you would be better off putting this code in the Before Update event procedure since the On Change will fire every time the user types a digit into the text box. So you will look up the zip and use the city and state info to populate the appropriate boxes. If the zip isn't found then use the following code:

DoCmd.OpenForm "FormName", , , acFormAdd, , ZipTextBox.Text

In your zip code form's Form_Load event procedure put:

ZipTextBox = Me.OpenArgs

This will get the zip code to your next form. After the user adds the city and zip and then closes the zip code form, to populate the first form's boxes use the following code in the Form_BeforeUpdate procedure:

Forms!FirstForm!CityTextBox = Me!CityTextBox
Forms!FirstForm!StateTextBox = Me!StateTextBox

This will populate the original form. Of course you will need to substitute the appropriate names which match your forms and textboxes.

hth
Jeff Bridgaham

 

[sko]

If the zipcode field on your Broker form is a combo box, you can use it as a lookup for city and state.

Set the combo box row source property to a query that brings in the zip, city and state from tblZipcode:

SELECT [tblZipcode].[ZipI], [tblZipcode].[city], [tblZipcode].[state] FROM tblZipcode ORDER BY [tblZipcode].[ZipI];


Set column count to 3 and column widths to 1";0";0"
Set bound column to 1 (zipcode column)

Since you want the city and state fields on your form to auto populate whenever the zipcode field is updated, code the combo box AfterUpdate event:

Private Sub cboZipcode_AfterUpdate()

Me.NameOfCityFieldOnBrokerForm = cboZipcode.Column(1)
Me.NameOfCityFieldOnBrokerForm= cboZipcode.Column(2)

End Sub

Your ComboBox has 3 columns, zipcode city state. The code .Column(1) is referencing the second column (zero-based) and placing the value in the city field of your form.

Let's go back to the combo box properties for a minute. Set "Limit to List" to yes. Now, if the user tries to enter a value not on the list the NotInList event is triggered. This is where you can place code to open your zipcode form.

Private Sub cboZipcode_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim Msg As String

If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list. Add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmZipcode", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The NotInList procedure has two arguments. NewData refers to the new zipcode the user entered in the combo box. The Response argument tells the procedure what to do after your code executes. Response = acDataErrAdded requeries the combo box.

Look at the DoCmd line for a minute. The last argument (called OpenArgs) is set to NewData. This is passing the new zipcode to the zipcode form when it opens.

Open your zipcode form in design view. Code the "OnLoad" event

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me![ZipI] = Me.OpenArgs
Me![city].SetFocus
End If

End Sub

This code places the value of OpenArgs (which is the new zipcode held in NewData from your NotInList procedure) in your zipcode field and sets the focus to the next field in the form.

HTH

 

[Steve001]

Hello :

First I'd like to thank all contributors out there I've learned a fantastic amount just lurking here. This is a really great learning tool. But now I'm stumped. I've used the techniques described above to auto populate from a combo box but cannot get the data to store in the the table or query, Though anything entered by keystroke does appear!

Probably something simple - but it's got me stumped.

Thanks to all in advance

Steve

 

[sko]

Are you sure you're coding the combo box after update event?
Post the sql from your combo box row source property.

 

[Guest_imported]

All, thanks for the ideas and support. I actually used a bit of both to code my approach. The best part is learning and understanding.

Thanks again.