Automatic entering of values from linked table

[psuplat]

Here's a nice one for ya

My database has a main table and a linked table from a different database.
My table, among others has the fields Serial_No and Tracker_No, linked table has the fields Serial_Is and Tracker_Is (among others of course but those are the ones that interest me). Table are related Serial_No<->Serial_Is in a one-to-one relationship.

On the form I have a field for Serial_No, and Tracker_No.

What I want is when I input the serial number the access automatically looks up the same number in the linked table (by relation), gets the Tracker_Is value puts it in the Tracker_No field (and adds it to my table, of course)

Thanks from the top for any help

 

[pono1]

It's do-able...

As a place to start, I suggest you search for and read up on the following form events in Access Help, here in the forum and on Google: After Update and Before Update. You will want to put code in one of these event handlers (I'm guessing most people would use the After Update event) that inserts your record... To get a sense of how you might do the record insert in code, search on SQL and Insert Statement here and in Help.

This may be overkill for your purposes, but you may also want to search on BeginTrans in the Help file.

Regards,
Tim

 

[psuplat]

Could I hope for some kind of example code for that. I'm reeeeeallllly crap when it comes to "code writing" programming (shame on me)

 

[psuplat]

I've found something like this on the net, might work:

When the Microsoft Visual Basic window appears, you can create a new function to retrieve the value from the table. In our example, we're going to create a function called GetGST that retrieves the GST field from a table called GST.
We've pasted the following code into our new module:

Function GetGST() As String
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LGST As String
'Open connection to current Access database
Set db = CurrentDb()
'Create SQL statement to retrieve value from GST table
LSQL = "select GST from GST"
Set Lrs = db.OpenRecordset(LSQL)
'Retrieve value if data is found
If Lrs.EOF = False Then
LGST = Lrs("GST")
Else
LGST = "Not found"
End If
Lrs.Close
Set Lrs = Nothing
GetGST = LGST
End Function
​

I can simply change that GST to Track and get the Track_Is number.
I was thinking to get the value from Serial No field on the form, and use it to find the track_is in the other table using the 1-to-1 relation.
Now, that's theory. Can somebody show me something practical that I could use, please.