 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

March 8th, 2005, 12:26 PM
|
Registered User
|
|
Join Date: Mar 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Auto Populate fields in a form
Hello, I am trying to to pull information from a record in a table into a form based on the entry of data in another field in the form that exists in the table.
E.g., if I type in a product number, I want other attributes/details of the product pulled into the form via autopopulation as well. I am trying to use the afterUpdate event, but I am not sure how to get the information to pull over.
Make sense? Any help is appreciated.
Matt Nemec
Business Developer
Cardinal Health
|

March 8th, 2005, 12:31 PM
|
Friend of Wrox
|
|
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
|
|
I do a similar thing with a form to edit Insurance Policy information. I have a combobox with each possible Policy Number and then run the following code on the OnClick event.
Private Sub cboFindPolicy_Click()
DoCmd.GoToRecord , , acGoTo, cboFindPolicy.Value
End Sub
I would think that you could probably use a textbox as well, but you might need to add some additional validation code.
Hope that helps, let me know if I need to elaborate.
Mike
Mike
EchoVue.com
|

March 8th, 2005, 12:34 PM
|
Friend of Wrox
|
|
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
|
|
I should probably have added that the form was set up with the data table containing all the details as the recordsource for the table, so when you first open the form, the first policy is displayed on the screen.
Mike
Mike
EchoVue.com
|

March 9th, 2005, 01:46 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
echovue, there isn't an OnClick event on a combo box. Do you mean the AfterUpdate event, or is there another button to click to get the update to appear?
This all depends on whether this is a bound or unbound form.
Bound form:
If the detail you are trying to pull in exists in the same record as the Product number, then use the combo look up wizard to find a record in your form, and then just drop the requisite text box on the form and go to Properties>Data and select Enabled-No, Locked-Yes. This will display the data without allowing users to modify it. Don't forget to add this to the form's On Current Event:
cboCustomerLookUp = CustomerID
This will keep the combo box current as you scroll through records.
Unbound form:
Create the form as bound first, and then delete the recordsource.
Create a combo box to look up the Customers in your Customers table, and then on the combo box's After Update event put this sort of code:
Me.RecordSource = "SELECT * from tblCustomer WHERE CustomerID = " & Str(cboCustomerLookUp)
I use a look up on my main form where the user selects a Customer BEFORE they open the form, and then give them a second look up on the form. This keeps the data calls to a minimum since they are only pulling one record at a time.
I hope this helps.
mmcdonal
|

March 9th, 2005, 01:49 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
I forgot to mention that if the data you want to display is in a second table, then you can create a subform and lock it. You can even format it to look like text on the main form if you flatten the subform and make the border invisible, etc. I use this on some of my forms.
I create a query with the data I want to display, then create a signle form view of the query. Then drop it on the main form, hide the outlines, and lock the data. It looks like text printed on the main form.
HTH
mmcdonal
|

March 9th, 2005, 01:54 PM
|
Friend of Wrox
|
|
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
|
|
That is definitely a better solution - I may have to implement it instead of my other one!
I am curious though, my ComboBox control has an OnClick event listed. I am using 2003 but I seem to remember 2000 having it as well, although I could well be mistaken.
Thanks
Mike
Mike
EchoVue.com
|

March 9th, 2005, 02:07 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
Good grief, you are right about the On Click event. I stand corrected.
mmcdonal
|

March 9th, 2005, 02:11 PM
|
Friend of Wrox
|
|
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
|
|
No worries!! The rest of the suggestion more than made up for it!
Mike
Mike
EchoVue.com
|

March 9th, 2005, 02:11 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
BTW, the code for the main form goes like this:
Combo Box: Look up meaningful data in the main table, like Customer Name (or Lastname, FirstName)
Button Text: Find this Customer
Button On Click Event:
'=====
Dim stDocName As String
Dim inCustID As Integer
Dim stLinkCriteria As String
inCustID = Me.cboCustomerLookUp
stLinkCriteria = "[CustomerID] = " & inCustID
stDocName = "frmCustomers"
DoCmd.OpenForm stDocName, , , stLinkCriteria
'=====
This opens the form with one record, then I use the code above to look up additional customers once the form is opened.
mmcdonal
|
|
 |