Populating subform after combo box seletion

[Reaper]

My main form has a subform that has a one-to-many relationship.


It is a work order system that could have many entries related to the single work order. A line is created in the WorkData table when he signs on. When the user is signing off of the work order I want him to be able to select and display only his entry.
I elected to have only one entry shown in the sub form at a time instead of showing all entries. The user will select his entry from a combo box that is populated by a query that restricts the list to only those names currently signed onto that work order. Then the user can enter his comments, click the "Save" button and exit.

My problem is that when the user name is selected:
1. I can't get it to go get the data from the table/appropriate line, and populate the data already given (Start date/time, etc.)
2. It creates a new entry in my WorkData table instead of editing the already existing entry. (Kind of explains #1?)

Sample database is attahced.

Thanks

[June7]

Need more info on process. Where do I start?

Where is user name 'selected' - frmSignOn?

[Reaper]

Open the form "frmOpenWorkOrders" and then select work order #62. This will open a form showing the work order info. Then you can click "Sign On" or "Sign Off". There is already a name signed on to the work order so if you click "Sign Off" it will get you to the form in question. The main form populates with basic Work Order info. The sub form is where the problem is. I want to use the "Tech Name" combo box to pull up the specific information about the tech's sign on. At this point the only information would be the Start Date/Time. The Stop Date/Time is defaulted to Now(). Then the user will add comments about what work was performed and then he can also change the status of the work order by selecting a new status from the combobox in the main form. I'll add programming for that once I get this issue solved.

What's happening is that instead of populating with existing information, after selecting the "Tech Name", it creates a new empty line. I need for it to pull up data from the existing line that is already there. I tried changing the parameters: "Data Entry", "Allow Additions",etc. but without luck.

Oh, the field at the bottom of the sub form is just the work order #. I left it visible for troubleshooting.

[June7]

One thing I notice right off is the tables are not linking on designated primary key fields.

You are saving employee name into tblWorkOrder instead of the record ID.

Same for txtMachineID#, txtDepartment, intLevelNumber, intWorkOrder#.

The primary key ID fields are not being used so why have them? Either use them or eliminate and set the fields that are being used as PK.

Professional developers will advise against using text fields as primary keys because they index slower.

Suggest you fix the PKs one way or the other and then provide revised db for analysis.

Should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be intWorkOrderNum.

[Parsonshere]

I notice the sub is onchange. Change it to "Private Sub txtTechName_Change()", and be sure the combobox has "Event Procedure" selected in the properties dialog.
Also, your field name and combobox name are exactly the same, Access doesn't know which one to address.
Always precede field names with fld, textbox with txt, comboboxes with cmb, etc. to make it easier to distinguish what is being address for you as well as Access.
after the subform name, add .form or better yet, comment that whole line and try something like:
Private Sub txtTechName_Change()
Dim x
On Error GoTo Err_Handler
x = Me.cmbTechName.Value

Me.RecordSource = "SELECT [tblWorkData].[intWorkOrder#], [tblWorkData].[txtWorkComments], [tblWorkData.txtFailCode], [tblWorkData.txtTechName], [tblWorkData].[dtWorkStartDateTime], [tblWorkData].[dtWorkStopDateTime] " & _
"FROM tblWorkData " & _
"WHERE (((tblWorkData.fldTechName) Like '" & x & "*'; " 'This forces the data to refresh, note the wildcard and the ' to denote text on each side of the variable.
' [Forms]![frmWorkRecord]![subfrmWorkRecordSub].Form.Requery 'This line is commented out
Exit Sub
Err_Handler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description, vbCritical, "Form Cancel Error"
Exit Sub
End Sub

[Parsonshere]

Also, to use the ID's like moderator said, change your combobox properties.
Set Data Source to something like "SELECT tblEmployees.ID, tblEmployees.txtEmployeeName FROM tblEmployees WHERE ((([tblEmployees]![txtCurrentAssign]) Like [Forms]![frmWorkRecord]![txtMachineID]));"
Then on the format tab of combobox properties,
1. Change column count to 2.
2. Change the width to: 0;3
Then instead of using the "like" operator, use = ID.
In the combo box itself, only the name will show, but the hidden ID is the default value (which is column(0), to reference the name in code use column(1))
Rewrite the rest of the code to match.

[Reaper]

Thanks Parsonshere. Very helpful.

Going back to June's initial comments about PK's. I want to address this before I get any further into the project.

Quoting:
"The primary key ID fields are not being used so why have them? Either use them or eliminate and set the fields that are being used as PK.

Professional developers will advise against using text fields as primary keys because they index slower.

Suggest you fix the PKs one way or the other and then provide revised db for analysis."
End Quote:

So, in the "tblWorkOrder" table the relationships created with supporting tables should all be linked back to the PK for each? This means changing the corresponding fields in "tblWorkOrders" to a number field, then creating a relationship to the PK. For example, change txtDepartment to intDepartmentID, then create a relationship back to "anDepartmentID" in "tblDepartment", and so forth. Correct?

If so, how do I go about displaying the actual information I want to show the user of my form (frmOpenWorkOrders), ie., the Department Name and not the ID num?

[June7]

Options for displaying related info:

1. include the lookup table in the form RecordSource, join type "Include all records from {data table} and only those from {lookup table} that match", bind textboxes to the lookup table fields but don't allow edit - set them as Locked Yes, TabStop No.

2. multi-column combobox - expression in textbox ControlSource can reference columns of the combobox

3. DLookup()

[Reaper]

Thanks evryone. I've been working when I can to incorporate your suggestions. I'm not quite back to the point to address my original question. Thanks for your patience.

[Reaper]

Ok, I've finally gotten caught up. I decided to go a different direction altogether so my original inquiry is moot. Thanks for your help and advice. I've incorporated your suggestions into my work.