If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto-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. -- DSM |
#2
|
|||
|
|||
Auto-populate a form
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. -- Dave Hargis, Microsoft Access MVP "Dave" wrote: 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. -- DSM |
#3
|
|||
|
|||
Auto-populate a form
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. -- hth 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." "Dave" wrote in message ... 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. -- DSM |
#4
|
|||
|
|||
Auto-populate a form
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! "Al Campagna" wrote: 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. -- hth 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." "Dave" wrote in message ... 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. -- DSM |
#5
|
|||
|
|||
Auto-populate a form
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! |
Thread Tools | |
Display Modes | |
|
|