I have the following linked tables:
tblMainPL is my main table that I need to pull the information in from. It has the following fields:
Autonumber1 -PK set as autonumber
Date - short date format-example: (10/22/08)
Load Number -Number-Single (should not be higher than 35-40 per day)
WorkOrder -Text- 50 (string of letters and numbers of varying length)-example-KR53103 2625000-48
Badge -text- (employee badge number)-example-c12345
There are a few other fields, but they are not important.
tblBondLayup is the second table. It has the following fields:
Autonumber -PK-set as autonumber
LayUpBadge -Text- 50-example-c12345
LayUpDate -date- short date-example-10/22/08
LayUpTime -Medium Time-example-06:24am
AdhesiveRollNum -number-Single
AdhesiveRollLot -number-Single
I need to pull the next three fields into the tblBondLayup with the use of a query input box by WorkOrder.
WorkOrder -Text-50-example-KR53103 2625000-48
Date -short date format
Load Number -number-Single
I created a form that OnOpen calls a query to get the WorkOrder, but I cannot get the results to display into the text box for tblBondLayup. I want the end users to scan a WorkOrder and those three fields fill in (but cannot be changed). The users then populate the other fields and move on to the next WorkOrder.
I did not use the WorkOrder as the primary key because there is a possibility of having to rework a WorkOrder several times and I need to be able to track that.
What I have so far is the WorkOrder, Date, and Load Number text boxes set to the query results. It appears to work, but will not populate the text box with the appropriate information. Instead, it gives me a #Name? error.
I also have created a query that returns the information that I need, but I cannot get it to post to the form (and update the table). I can get it to go into a form, but then I cannot input another WorkOrder to look up the next WorkOrder.
Thanks for all your help.
Hi,
The best way to handle this is to "Normalize" your tables. This prevents your database from being filled with duplicate data, and makes your database easier to use.
The recommended method to accomplish this is to add your tblMainPL Autonumber1 Field to the tblBondLayup Table as a Foreign Key and enforce Referential Integrity.
But you also need to correct a serious problem. "Date" is a Function used by Access to return today's date. Using it as a field in a Table will cause you a lot of problems. It is best to change the name to something like WODate.
Once these things are done, you can use a query for your Form's RecordSource:
- SELECT tblBondLayup.Autonumber, tblBondLayup.LayupBadge, tblBondLayup.LayUpDate, tblBondLayup.LayUpTime, tblBondLayup.AdhesiveRollNum, tblBondLayup.AdhesiveRollLot, tblMainPL.AutoNumber1, tblMainPL.WODate, tblMainPL.[Load_Number]
-
FROM tblMainPL INNER JOIN tblBondLayup ON tblMainPL.Autonumber1 = tblBondLayup.Autonumber1;
Now you can use an *Unbound* ComboBox to go to the desired WorkOrder.
We will call it cboGoToWorkOrder. Set the column widths to 0";1.5". Your ComboBox RowSource query should be
- SELECT tblMainPL.AutoNumber1, tblMainPL.WorkOrder FROM tblMainPL ORDER BY WorkOrder;
The 0 widths for the column gets the field data but hides it so all you see in the Combo Box is your WorkOrder.
Use the AfterUpdate Event of the Combo Box with the following Code:
- Private Sub cboGoToWorkOrder_AfterUpdate()
-
-
Dim rs As DAO.Recordset
-
-
If Not IsNull(Me.cboGoToWorkOrder) Then
-
'Save the current record.
-
If Me.Dirty Then
-
Me.Dirty = False
-
End If
-
'Search in the clone set.
-
Set rs = Me.RecordsetClone
-
rs.FindFirst "[AutoNumber1] = " & Me.cboGoToWorkOrder
-
If rs.NoMatch Then
-
MsgBox "The Work Order was Not Found."
-
Else
-
'Display the found record in the form.
-
Me.Bookmark = rs.Bookmark
-
End If
-
Set rs = Nothing
-
End If
-
-
End Sub
One last step: In the Properties of the WODate and Load_Number TextBoxes click the Data tab and set the "Locked" Property to Yes. This will display the information but not allow the users to change it.
You can download some databases from my site that uses these methods: http://gainingaccess.net
Best Regards,
Patrick Wood