Populate Fields from Combobox

[Samurai1974]

Reference https://www.accessforums.net/access/...kup-21396.html



Hi, everyone! I know I'm 2 years behind on this issue, but I'm hoping someone could help. I have used the second method suggested by pbaldy but it only works for one of the columns. I have a table "IssueLog" with a combo box (Inmate) that hooks up to a query that feeds from another database. All of the fields shown below are in that query. I want the user to select an inmate from the combo box, and have the rest of their info fill in automatically. But... I also want all that info copied into the "IssueLog" table.

Here's the code I used in the Inmate_AfterUpdate()

Me.Number = Me.Inmate.Column(1)
Me.Gender = Me.Inmate.Column(2)
Me.DOB = Me.Inmate.Column(3)
Me.SecRating = Me.Inmate.Column(4)
Me.Custody = Me.Inmate.Column(5)
Me.AdmitDate = Me.Inmate.Column(6)
Me.ERDate = Me.Inmate.Column(7)
Me.CaseManager = Me.Inmate.Column(8)

When I open the form and choose an Inmate, the Number field populates, but none of the other ones. The column numbers I am using match the columns in my query, so I'm not sure what I'm doing wrong.

Does anyone have any ideas?
Steven

[June7]

Why duplicate data? Why would you also save the inmate DOB, AdmitDate, etc. into IssueLog? Why not just save the Inmate id into the IssueLog?

Number is a reserved word. Should avoid reserved words as field names. https://support.office.com/en-US/Art...rs=en-US&ad=US

[pbaldy]

Check the column count property of the combo.

[Samurai1974]

June7: I have to save the data each time (I know it's sloppy/bloaty) because sometimes inmates leave and then come back again at a later date. While some fields stay the same (name, number, gender, and DOB) the other ones may change the next time they come back in. The name field is fed from a query (qryInmateList) which feeds off of a linked table to another database. I'm just linking it so I always have the most current incarceration information of any given inmate.

I'll check into the reserved word possibility, but like I said, Number is the only field that *does* work...lol... so I'm not sure that's the issue.

pbaldy: The column count for the Inmate field in the IssueLog table is currently set to 1, as is the column count on the Inmate field on my "EntryForm" form. However there are 10 columns in the qryInmateList that that field feeds off of. Should I increase my column count to 10? (I guess I'll try that while I see what you say...lol)

[Samurai1974]

PS: I just realized that I can't test this until tomorrow when I'm back at work... At home I have no link to the other database.

[June7]

What is this IssueLog documenting - behavior? Still think should not save this data in IssueLog - certainly not DOB, gender. If you want history of inmates comings and goings, should be another table for that.

[Samurai1974]

The IssueLog table is actually called tblSeparateConfinement... it's a log for tracking and generating paperwork related to holding inmates in Separate Confinement or Segregation. Currently users generate paperwork in Word, and then someone else plugs that data into a different dbase for stats purposes. In the process many copies are made, signatures get lost, stats aren't entered properly, etc and it's generally a giant mess (the Ombudsman is not happy with our record keeping in this regard). I'm making a new dbase to force users into entering into the dbase *first* and then it will generate the paperwork required by our Corrections Act (and also eliminating a double-entry, paperwork-then-dbase setup). The top portion of the paperwork/report has to contain certain information from a legal standpoint, and so it needs to be included on the table.

Our systems dept is working on an all-encompassing database that does this and WAAAY more, but it's still years in the making... I need something relatively "right now" in order to clean up our paperwork/filing nightmare. I would normally not save the same info over and over again on a table (this isn't my first dbase), but for this application I'm afraid that's what I have to do.

I've just never tried to auto-populate TxtFieldB based on a selection from ComboFieldA before so that's why I'm here.

[June7]

Okay, so the expressions referencing the combobox columns should work. Did you check the ColumnCount property as suggested by Paul in post 3?

[pbaldy]

Okay, so the expressions referencing the combobox columns should work. Did you check the ColumnCount property as suggested by Paul in post 3?

Addressed in post 4 June7.

[June7]

Oh, right, so now is wait and see.

[Samurai1974]

Sorry for the delay, I had to try everything out at work. The good news is everything works great now that I've changed my ColumnCount to 10 in the table and form.

I also noticed that I didn't have my code referencing the correct columns from the query. I've changed around the order of my query to match the form and now everything works great!

Thanks for your help, guys!!

Steve

[pbaldy]

Happy to help!