Match/Duplicate/Auto fill certain fields from different tables

L

LJ

Okay Lets try this forum:

I have Two different tables...They are linked, relationship between them is
the Order Number field...Now on the second table one I would like to just
type
in the order number and have two to three field copied from the first
table...you know instead of all 15 fields.

So 1st form/Table:

Order Num:
Customer:
Time:
Date:
Lic:
Etc:

Now i would like to:
on 2nd Form/Table

Type in
Order Num: ######

and get

Customer: Auto (Based on what is entered in 1st table/form)
Lic: Auto ( " ")


Is there a code for this or is it simpler that this...
 
C

CompGeek78

Okay Lets try this forum:

I have Two different tables...They are linked, relationship between them is
the Order Number field...Now on the second table one I would like to just
type
in the order number and have two to three field copied from the first
table...you know instead of all 15 fields.  

So 1st form/Table:  

Order Num:
Customer:
Time:
Date:
Lic:
Etc:

Now i would like to:
on 2nd Form/Table

Type in
Order Num: ######

and get

Customer: Auto (Based on what is entered in 1st table/form)
Lic: Auto ( "     ")

Is there a code for this or is it simpler that this...

UNTESTED AIR CODE:

You'll want to use something along these lines in order to accomplish
this:
Dim rs As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM table1 WHERE OrderNum = " & Me.OrderNum

Set rs = Currentdb.OpenRecordset(sSQL, dbOpenSnapshot, dbReadOnly)
If rs.EOF Then
MsgBox "No matching record."
Else
Me.Customer = rs!Customer
Me.Lic = rs!Lic
End If

rs.Close
Set rs = Nothing

Modify this for your particular structure and add it to the
AfterUpdate event of the field you want it to trigger off of.

Keven Denen
 
L

LJ

Guide me through this:

So first table/form called: "CheckInTicket"
Fields in the table:
[Order/Pick-up Num]:
[Custober/Buyer Name]:
[Lic]:
Etc:

Second Table/form called: "LoadingCheckList"
Fields in table:
[Order Num]:
[Loading Date]:
[Customer]:
[Lic]:

On this Table/form (LoadingCheckList) under [Customer] {AftrUpdate} i placed
the code after trying to modify it to my content nothing happens....

Help

and thank you very much for your responds and efforts in advance
 
C

CompGeek78

Guide me through this:

So first table/form called: "CheckInTicket"
   Fields in the table:
[Order/Pick-up Num]:
[Custober/Buyer Name]:
[Lic]:
 Etc:

Second Table/form called: "LoadingCheckList"
Fields in table:
[Order Num]:
[Loading Date]:
[Customer]:
[Lic]:

On this Table/form (LoadingCheckList) under [Customer] {AftrUpdate} i placed
the code after trying to modify it to my content nothing happens....

Help

and thank you very much for your responds and efforts in advance

UNTESTED AIR CODE:
You'll want to use something along these lines in order to accomplish
this:
    Dim rs As DAO.Recordset
    Dim sSQL As String
    sSQL = "SELECT * FROM table1 WHERE OrderNum = " & Me.OrderNum
    Set rs = Currentdb.OpenRecordset(sSQL, dbOpenSnapshot, dbReadOnly)
    If rs.EOF Then
        MsgBox "No matching record."
    Else
        Me.Customer = rs!Customer
        Me.Lic = rs!Lic
    End If
    rs.Close
    Set rs = Nothing
Modify this for your particular structure and add it to the
AfterUpdate event of the field you want it to trigger off of.
Keven Denen

You keep saying table/form...which are you using, a table or a form?
Two different objects.

If you are using a form, yes. Go to the AfterUpdate event of the
Customer textbox, click the build button (...). Choose Code Builder.
In between the lines that say "Private Sub Customer_AfterUpdate()" and
"End Sub" paste the code I wrote above.

Then, when you edit your customer name on the form it should either
fill the data in automatically or it should give the error message
that there is no matching record.

Keven
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top