Query results in TextBox

psuplat

Registered User.
Local time
Today, 07:14
Joined
Mar 18, 2009
Messages
35
Hi.

I'm trying to display result of a query in a textbox on a form and I'm hitting a wall.

I've got 2 tables - Test & Tracker joined 1-to-1 on SerialNo and SN.
table Test already has values inputed for field SerialNo and Tracker.
On the form (working on Tracker table) after inputing the serial number access automatically goes to next record (tracker field is not on the tabbing list).

What i want is on AfterUpdate event for Serial_No for a query to be run in Track_No. Query looks in "Test" table, in column SerialNo, for a value matching that one inputed in Serial_No TextBox, takes Tracker value from that record and inputs it into Track_No TextBox.

Private Sub Serial_No_AfterUpdate
Track_No = "SELECT test.Tracker FROM test WHERE (test.SerialNo="Serial_No");"
End Sub

But it doesn't want to work

And please don't laugh, I'm really crap in that kind of programming:(
 

Attachments

  • Picture 1.png
    Picture 1.png
    7.5 KB · Views: 140
  • Picture 2.png
    Picture 2.png
    15.6 KB · Views: 126
  • Picture 3.png
    Picture 3.png
    8.6 KB · Views: 138
First of all, let's get a description from you of what you are REALLY after (not what you are after with this as it doesn't make a bit of sense to be having the same fields stored in different tables).

What is your data and how does it relate to each other in the real world?
 
Ok, in real life situation looks like that.

During final inspection of a product I'm scanning in the serial number, and ticking check boxes confirming visual, functional and printout inspection. That's my database.

Now, the production has their own database that monitors every single product from the beginning of the line, throughout all the stations, to it's end. Of course serial number is assigned to machine at the back of the line so at the beginning machines have a so-called tracking number. It's being scan in at the 1st station, than 2nd, and so on until it get to the last one where the proper serial number is being assigned.

My database has a linked table from productions database containing all the data.

On the form I have the following fields: serial_no, tracking_no, visual, function, print, fault_descr, inspector.

When I scan-in the serial number of the machine, I want Access to look up in the linked table the serial number that I've scanned-in, find the tracking number and place it in tracking_no textbox on the form.

I hope that explains what I'm trying to do.
 
Hello psuplat!

What I would do to achieve what you want to do, is to open a recordset of the table "Test", search the value there and then retrieve it into the Tracker_No textbox.
I wrote the code for you and I think it should work without problems, but if it doesn't work just post back and I will try to see what's wrong.
The code should look like this:

Private Sub Serial_No_AfterUpdate()
'-----------Declare the variables to be used----------------
Dim StrSerialNo as string, Criteria as string, TrackerNo
Dim db As Database
Dim TestRecordset as DAO.Recordset

'--------Set the database----------------
Set db = CurrentDb

'----------Open the table where you want to search fo the value as a Recordset-----------
Set TestRecordset = db.OpenRecordset("Test", dbOpenDynaset)

StrSerialNo = Serial_No.value '---->This is the serial number to look on the Test table

Criteria = "SerialNo = '" & StrSerialNo & "'" '-----> Criteria for the search

'-------------------Look for the Serial Number on the table Test grab the TrackerNo into TrackerNo variable----------
With FieldsRst
.FindFirst Criteria
TrackerNo = .Fields("Tracker").value
end With

'---------Place TrackerNo into Track_No Textbox---------------
Track_No.value = TrackerNo

'-----------Clean Objects------------
set db = Nothing
set TestRecordset = Nothing
End Sub

You could also update the value in your Tracker table directly, but you will have to write an Update query, and then requery you form so you will see the Tracker number in the Tacker_No textbox.
I hope I'm not being so confusing. I hope you can understand my English because it's not my first language.

I hope it will help, Cheers.
 
I'm getting a message saying

Run-time Error '424'
Object recquired

and in the code editor line .FindFirst Criteria gets highligted in yellow

With FieldsRst
.FindFirst Criteria
TrackerNo = .Fields("Tracker").value
end With
 
I'm sorry I copied it from a piece of code I have in one project and I forgot to change the name of the RecordSet. So, instead of:

With FieldsRst
.FindFirst Criteria
TrackerNo = .Fields("Tracker").value
end With

It should be:
With TestRecordset
.FindFirst Criteria
TrackerNo = .Fields("Tracker").value
end With

TestRecordset is the name I have given to the Recordset when I declared the object. I think all the rest of the code will be ok.
I hope it will help you. Good luck.
Cheers!!
 
Well, i did change it but know i'm getting different problem.

A message is saying:
Run-time error '3077':
Syntax erros in string in expression

and the same line of code is being highlighted
 
It has something to be with type of the serial number, I was assuming it is a string so I declared the variable as a string. You cannot pass a null value to this variable, and I think this is why it's giving you that error. Or if you know it's not a string change the type in the declaration or leave without a type and it's gonna be a Variant by default.
First line of code:
Dim StrSerialNo, Criteria as string, TrackerNo

Or it might be that your field is a number in this case I would declare the variable as a integer if it's so. And in the Criteria I would take off the quotations marks, like this:
Criteria = "SerialNo = " & StrSerialNo

Try it and see what happens; I can't exactly know what the error is because I don't know your data.
Good luck. Cheers!
 
Everything works fine. I've just missed an apostrophe in the line when I define criteria:)
 
That's good. I'm glad that worked for you.
Have a nice day. Cheers!
 

Users who are viewing this thread

Back
Top Bottom