473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
1 4233
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

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

Similar topics

6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access...
6
by: lesperancer | last post by:
SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMonth) Between . And .)); tblMonthlyBooking is a sql server...
6
by: gerbski | last post by:
Hi all, I am relatively new to ADO, but up to now I got things working the way I wanted. But now I've run into somethng really annoying. I am working in MS Access. I am using an Access...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
1
by: polyya | last post by:
Hi All, I'm working in Access 2000. Sorry for the long post, I'm trying to be as clear as possible. I've created junction tables to express M:M relationships for contractors and job task. ...
23
by: mlcampeau | last post by:
Hey guys, I have been mulling over this problem for a few days and have yet to come up with a query that will give me the expected results. I am working on a database that stores employee...
8
by: Gari | last post by:
Hello, I am trying to build a filter query with some AND and OR. I have three text boxes and 5 check boxes. The checkboxes are linked via code to other textboxes for the purpose of the query. ...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.