Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 8th, 2005, 12:26 PM
Registered User
 
Join Date: Mar 2005
Location: Columbus, OH, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #2 (permalink)  
Old March 8th, 2005, 12:31 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 8th, 2005, 12:34 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 9th, 2005, 01:46 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old March 9th, 2005, 01:49 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old March 9th, 2005, 01:54 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old March 9th, 2005, 02:07 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Good grief, you are right about the On Click event. I stand corrected.

mmcdonal
Reply With Quote
  #8 (permalink)  
Old March 9th, 2005, 02:11 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

No worries!! The rest of the suggestion more than made up for it!

Mike

Mike
EchoVue.com
Reply With Quote
  #9 (permalink)  
Old March 9th, 2005, 02:11 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Populating Fields in Form E3-PSU Access VBA 3 August 29th, 2007 04:43 AM
Auto populate/look up slim Access 6 May 31st, 2007 03:58 AM
Auto-populate table data into form jeff394 Access VBA 2 April 19th, 2006 02:55 PM
Auto Populate a field ? mar0364 Classic ASP Databases 4 July 9th, 2004 11:10 AM



All times are GMT -4. The time now is 06:24 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.