By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Inserting Query results into a text box on a form in linked tables

P: 39
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.
Oct 22 '08 #1
Share this Question
Share on Google+
1 Reply


P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblBondLayup.Autonumber, tblBondLayup.LayupBadge, tblBondLayup.LayUpDate, tblBondLayup.LayUpTime, tblBondLayup.AdhesiveRollNum, tblBondLayup.AdhesiveRollLot, tblMainPL.AutoNumber1, tblMainPL.WODate, tblMainPL.[Load_Number]
  2. 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
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboGoToWorkOrder_AfterUpdate()
  2.  
  3.    Dim rs As DAO.Recordset
  4.  
  5.     If Not IsNull(Me.cboGoToWorkOrder) Then
  6.         'Save the current record.
  7.         If Me.Dirty Then
  8.             Me.Dirty = False
  9.         End If
  10.         'Search in the clone set.
  11.         Set rs = Me.RecordsetClone
  12.         rs.FindFirst "[AutoNumber1] = " & Me.cboGoToWorkOrder
  13.         If rs.NoMatch Then
  14.             MsgBox "The Work Order was Not Found."
  15.         Else
  16.             'Display the found record in the form.
  17.             Me.Bookmark = rs.Bookmark
  18.         End If
  19.         Set rs = Nothing
  20.     End If
  21.  
  22. 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
Oct 24 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.