 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Auto-populate a form
Is there a way to auto-populate a form using data indexed to a primary key in a table? That is, when I enter the primary key value in a form, I want the remaing fields to automatically pull data from a table with that primary key and populate the remaining fields.  SignatureDSM I would suggest an unbound combo with a row source based on your primary key field. Then use the After Update event of the comb to locate the record and make it the current record: Private Sub MyCombo_AfterUpdate() With Me.RecordsetClone .FindFirst "[PrimeKeyField] = " & Me.MyCombo If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With End Sub Note, the syntax on the FindFirst line assumes [PrimeKeyField] is numeric. It it is text: .FindFirst "[PrimeKeyField] = """ & Me.MyCombo & """" For A Date field .FindFirst "[PrimeKeyField] = #" & Me.MyCombo & "#" And, of course, use your field and control names. SignatureDave Hargis, Microsoft Access MVP > Is there a way to auto-populate a form using data indexed to a primary key in > a table? That is, when I enter the primary key value in a form, I want the > remaing fields to automatically pull data from a table with that primary key > and populate the remaining fields. Dave, You'll need to capture the key value (ex. CustID = a bound field), but you don't want to capture the ancillary information. (Name, Address, City, etc...) You just want to "display" it on the form. Once you've captured the CustID, you can always re-derive the ancillary information... in any subsequent form, query, or report. A popular method is to use a multi-column combo box (ex. cboCustID) to select the CustID. Example cboCustID column setup... CustID CustName CustAddress 142 Jones 12 Main St cboCustID Combobox properties... ControlSource = CustID No of colums = 3 Column Widths = 0"; 1.5"; 2.0" What this does is... it allows the user to select a CustID by CustName, the combo displays CustName, but what's really stored in the CustID field in the table is... the CustID. Now, given an "unbound" text control, on that form, with a ControlSource of... =cboCustID.Column(2) will always "display" the CustAddress (not capture it, but just display it). Combo colums are numbered 0, 1, 2, 3, etc..., so column 2 would be the CustAddress value. On my website (below) I have a 97 and 2003 sample file called "Combo populates multiple fields", that shows how this is done. Signaturehth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." > Is there a way to auto-populate a form using data indexed to a primary key > in [quoted text clipped - 3 lines] > key > and populate the remaining fields. Can someone explain how I would do this with an input field rather than a drop-down combo box? Here is my problem: I am trying to edit a db that someone else created. There is one table of data (RUNNERS). There is one form (BIB LOOKUP). The goal is that on the form you would type the BIB NO into a field and all other runner info would populate. I am not sure how this is accomplished - but it is working. My issue is that I have a new set of data to load - with different column names. I have replaced the data into RUNNERS and now I need to adjust the form to read my new data, but I cannot figure out how this is done. In the control source of the field which need to be populated, it says: =[BIB LOOKUP].COLUMN(2) If BIB LOOKUP is the name of the form itself, so I do not see how that is reference my table RUNNERS. Obviously the column numbers are different, but what else needs to be changed to avoid the #NAME? that I am receiving as output? Very confused, any input would be greatly GREATLY appreciated! > Dave, > You'll need to capture the key value (ex. CustID = a bound field), but [quoted text clipped - 34 lines] > > key > > and populate the remaining fields. Can anyone explain how I would do this with an input field instead of a combo box? Here is my issue: I am trying to edit a db that someone else created. There is one table of data (RUNNERS). There is one form (BIB LOOKUP). The goal is that on the form you would type the BIB NO into a field and all other runner info would populate. I am not sure how this is accomplished - but it is working. My issue is that I have a new set of data to load - with different column names. I have replaced the data into RUNNERS and now I need to adjust the form to read my new data, but I cannot figure out how this is done. In the control source of the field which need to be populated, it says: =[BIB LOOKUP].COLUMN(2) If BIB LOOKUP is the name of the form itself, so I do not see how that is reference my table RUNNERS. Obviously the column numbers are different, but what else needs to be changed to avoid the #NAME? that I am receiving as output? Very confused, any input would be greatly GREATLY appreciated! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.