Append query Help!

[dbay]

I need help with writing some kind of Append query. I do not know much about SQL and I can not seem to find any examples on what I'm trying to do.
This will probably be simple for most of you.

I have 2 tables. The first table is called tblTempList which is where I am importing a list of VINs into from an Excel sheet. The colum name is F1. The second table called tblVINList is a many relationship table. It has 2 colums. The first is called WorkOrderID and the second is VINList.

I have a subform called sfrmVIN with a subform on it called ssfrmVINList.
The form ssfrmVINList shows the list from the table tblVINList using the WorOrderID as its reference.

I need a method of getting the list from table tblTempList, colum F1, to table tblVINList colum VINList along with the subforms WorkOrderID number in the WorkOrderID colum of the table.

Example of tblVINList after transfer:

WorkOrderID | VINList
344 | C06P025655
344 | C06P025865
344 | C06P026157
344 | C06P026420
344 | C06P035832

This needs to be done from a Buttons click event procedure. Any help on this would be very appreciated.

 

[dbay]

Anyone There?

Any one have any ideas? anyone...

 

[dbay]

Maybe something like this?

Private Sub cmdTransferList_Click()
On Error GoTo Err_cmdTransferList_Click

Dim db1 As DAO.Database
Dim db2 As DAO.Database

Dim rsTemp As DAO.Recordset
Dim rsList As DAO.Recordset

Set rsTemp = db1.OpenRecordset("tblTempList")
Set rsList = db2.OpenRecordset("tblVINList")

rsTemp.MoveLast
rsTemp.MoveFirst

Do While Not rsTemp.EOF
rsList.AddNew
rsList.Fields("WorkOrderID") = Me.WorkOrderID
rsList.Fields("VINList") = rsTemp.Fields("F1").Value
rsList.Update
rsTemp.MoveNext
Loop

rsTemp.Close
rsList.Close

Set rsTemp = Nothing
Set rsList = Nothing
Set db1 = Nothing
Set db2 = Nothing

Exit_cmdTransferList_Click:
Exit Sub

Err_cmdTransferList_Click:
MsgBox Err.Description
Resume Exit_cmdTransferList_Click

End Sub

 

[Banana]

I'm just a bit confused about exactly how are you getting the values from Excel; are you using a maketable query or directly refering the worksheet?

I'm under the impression that you're using a maketable query and that is the basis for your rsTemp.

If so, remember this:

Action queries are always faster & better than looping via recordset in VBA. Only when you have really convoluted logic, do you need to use a recordset.

If you're using a MakeTable query, then you certainly can use this to make Append query. This can't get any simpler than selecting the MakeTable query as the source, the desintation table, and selecting appropriate fields from MakeTable query and select what fields it will append to.

But if you aren't making a MakeTable Query, and are somehow directly linking to the Excel worksheet, and can't/don't want to make a MakeTable query, then you do need to use a VBA recordset (but that's only because I have no clue how one would append from a excel worksheet to a table).

There are some flaws:

rsTemp.MoveLast
rsTemp.MoveFirst

You do not need to issue a MoveLast. This is processor-intensive task and you're forcing your users to take a coffee break. I don't think I need to add that MoveFirst is wholly unnecessary as well.

rsList.Fields("VINList") = rsTemp.Fields("F1").Value

You'd be inserting same value over and over because you specified F1, when it should be iterating over the range of F column from F1 to F-X.

I suppose this could work:

rsList.Fields("VINList") = rsTemp.Fields("F" + Format$(i))

But you'd need to use For...Next loop instead of Do While loop.

    rsTemp.Close
    rsList.Close
    
    Set rsTemp = Nothing
    Set rsList = Nothing
    Set db1 = Nothing
    Set db2 = Nothing

Exit_cmdTransferList_Click:
    Exit Sub

All cleanup code from that point should be *after* Exit_cmdTransferList_Click label. That way, if you have a fatal error, it'll still clean itself up.

 

[dbay]

Thank you for your reply Banana

I see it is my fault that my first post was not real clear about what I am trying to do. I have all the code for the importing of the Excel sheet. All of that works fine.

What I need to do is simply append the colum named "F1", from tblTempList to the colum named "VINList" in tblVINList. With one little exception. In the tblVINList there is a second colum named "WorkOrderID" which is the key colum for the list. I need that to come from the "WorkOrderID" number from the Form.

So I don't know how to write a query to make this happen...From what I understand you can not use a "Form control" like Form!frmWorkOrder![WorkOrderID] in an Append query. So I'm lost on what to do.

Example of what I want in the table tblVINList

1st. Colum.

WorkOrderID
343
343
343
343
(This is comming from the MainForm WorkOrderID)

2nd Colum.

VINList
c123456
c132564
c564654
c654654
(This is a list of VINs comming from the tblTempList)

Thank You

 

[Banana]

Of course you can use form control for an append control. I've done that myself.

If you're stumped on how to reference the forms' control as parameter, make this your bestest friend ever:

Expression Builder can be accessed in query builder, when you rightclick on a column and select "Build...". From there, you navigate to loaded forms and select the controls you want to reference.

Now here's a catch. If you want the control's value to be appended to the table, you don't store it as a criteria. Rather, you create a custom expression. Can be anything like Expr1: Forms!frmMyForm!MyControl. This will insert the ID from your control to the table along with the VIN.

Did that clarify matters?

 

[dbay]

Thank you again for your reply

Ok, I will try this when I get home from work. Thank you again for you reply.

 

[dbay]

Thank you Banana!

You were correct. It works perfect. Thank You!

 

[Banana]

Glad to know it worked.