[RESOLVED] Combobox and VBA help

[cubsm22p]

here is my code to a combobox that looks up [order number] values on the archivetbl , i want to append the record to another table called Order Master whats happening is i cant get the combobox value to work in the WHERE part of the code, it will only work when i put a value in the WHERE parts, and when it did work i got an extra field in my Master Order table:SEE ATTACHMENT

Private Sub Combo970_Change()
Dim rs As Object
 
    Set rs = Me.Recordset.Clone
     rs.FindFirst "[ID] = " & Str(Nz(Me![Combo970], 0))
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
Dim MyDb As Database
    Dim ArchiveSet As Recordset
    Dim CurrentSet As Recordset
    Dim StrSQL As String
    ' The fields below represent the fields you want to add to your new record
    ' So Name them appropriately and give them the correct datatypes
Dim Save2 As String
 
Dim Save4 As String
 
Dim Save7 As String
Dim Save8 As String
 
 
    StrSQL = "SELECT Archive.[Order Number], Archive.[Customer], Archive.[Sales Rep], Archive.[Parts] FROM Archive WHERE [Order Number] = '883014805225'" 'Me![Combo970]"
    Set MyDb = CurrentDb
 
' This opens the single "Template Record"
 Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
 
    With ArchiveSet
Save2 = ![Order Number]
'Save3 = ![Date Entered Into Alvin]
Save4 = ![Customer]
'Save5 = ![Received]
'Save6 = ![Need By]
Save7 = ![Sales Rep]
Save8 = ![Parts]
'Save9 = ![Scheduled Ship]
        .Close
        Set ArchiveSet = Nothing
    End With
 
    ' So we have captured all the data we want from the "Template"
    ' Now create the new record in the Current Table
    ' Assume that the field names are the same in both tables
 
    StrSQL = "SELECT [Order Master].* FROM [Order Master];"
 
    Set CurrentSet = MyDb.OpenRecordset(StrSQL)
 
    With CurrentSet
        .AddNew
        ![Order Number] = Save2
'![Date Entered Into Alvin] = Save3
![Customer] = Save4
'![Received] = Save5
'![Need By] = Save6
![Sales Rep] = Save7
![Parts] = Save8
'![Scheduled Ship] = Save9
 
        .Update
        .Close
        Set CurrentSet = Nothing
    End With
 
    ' So Now we have a new record in the table, its a matter of getting to it on the form
 
   ' Me.Requery
    '     DoCmd.GoToRecord acLast
End Sub

[westconn1]

get the combobox value to work in the WHERE part

should look like
WHERE [Order Number] = '" & Me![Combo970] & "'"

over all it would probably be better to use an insert query

[cubsm22p]

i have a button right now that uses an append query but im trying to find a faster/better and safer way to search for the order_numbers in the archive table to bring the record from the archive to the order master table

[cubsm22p]

i tried what u said it ran with out any error codes, but the order master table isnt being populated with any new feilds

Private Sub Combo970_Change()
Dim rs As Object
 
    Set rs = Me.Recordset.Clone
     rs.FindFirst "[ID] = " & Str(Nz(Me![Combo970], 0))
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
Dim MyDb As Database
    Dim ArchiveSet As Recordset
    Dim CurrentSet As Recordset
    Dim StrSQL As String
    ' The fields below represent the fields you want to add to your new record
    ' So Name them appropriately and give them the correct datatypes
Dim Save2 As String
 
Dim Save4 As String
 
Dim Save7 As String
Dim Save8 As String
 
 
    StrSQL = "SELECT Archive.[Order Number], Archive.[Customer], Archive.[Sales Rep], Archive.[Parts] FROM Archive WHERE [Order Number] = '" & Me![Combo970] & "' " 'Me![Combo970]"
    Set MyDb = CurrentDb
 
' This opens the single "Template Record"
 Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
 
    With ArchiveSet
Save2 = ![Order Number]
'Save3 = ![Date Entered Into Alvin]
Save4 = ![Customer]
'Save5 = ![Received]
'Save6 = ![Need By]
Save7 = ![Sales Rep]
Save8 = ![Parts]
'Save9 = ![Scheduled Ship]
        .Close
        Set ArchiveSet = Nothing
    End With
 
    ' So we have captured all the data we want from the "Template"
    ' Now create the new record in the Current Table
    ' Assume that the field names are the same in both tables
 
    StrSQL = "SELECT [Order Master].[Order Number], [Order Master].[Customer], [Order Master].[Sales Rep], [Order Master].[Parts] FROM [Order Master];"
 
    Set CurrentSet = MyDb.OpenRecordset(StrSQL)
 
    With CurrentSet
        .AddNew
        ![Order Number] = Save2
'![Date Entered Into Alvin] = Save3
![Customer] = Save4
'![Received] = Save5
'![Need By] = Save6
![Sales Rep] = Save7
![Parts] = Save8
'![Scheduled Ship] = Save9
 
        .Update
        .Close
        Set CurrentSet = Nothing
    End With
 
    ' So Now we have a new record in the table, its a matter of getting to it on the form
 
   ' Me.Requery
    '     DoCmd.GoToRecord acLast
End Sub

[cubsm22p]

Hey Thanks alot just got it had to deal wil nulls using Nz()

[cubsm22p]

One more question how do i use multiple lines with sql in vba EXAMPLE:

StrSQL = "SELECT Archive.[Order Number], Archive.[Customer],Archive.[Received],Archive.[Need By], Archive.[Sales Rep], Archive.[Parts]Archive.[Scheduled Ship], Archive.[Width],Archive.[Length],Archive.[Quantity], Archive.[Delivery Method], Archive.[Midax],Archive.[WCSS], Archive.[Job Type],Archive.[Why],Archive.[Service Rep], Archive.[Why Reop], Archive.[Plate Date],Archive.[Press Date], Archive.[Press Sequence],Archive.[Collator Date],Archive.[QOffline Date], Archive.[Paper Due], Archive.[Carbon Due],Archive.[Die Due],Archive.[Ink Due], Archive.[Repeat Order Numbers],Archive.[Misc Due],Archive.[Companion Order], Archive.[Companion Order Number], Archive.[Press]Archive.[No Parts], Archive.[No Wide],Archive.[Calc Length],Archive.[Speed]"
    "Archive.[Calc Hrs], Archive.[Completed],Archive.[Remaining],Archive.[Additional Hrs], Archive.[No of Inks Face], Archive.[No of Inks Back],Archive.[Die Number(s) Face], Archive.[Die Number(s) Liner],Archive.[Collator No:],Archive.[Calc Collator Hours], Archive.[CompletedC]
    Archive.[RemainingC],Archive.[Collator Delivery], Archive.[Offline Machine No],Archive.[Calc roto Hrs],Archive.[Feet Per Core roto], Archive.[Order Complete], Archive.[Collator Speed],Archive.[Offline Speed],
    Archive.[No Wide Thru Collator],Archive.[Calc Length Thru Collator],Archive.[No Wide Thru Roto], Archive.[Calc Length Thru roto], Archive.[Completed roto Hrs],Archive.[Remaining roto Hrs],Archive.[Offline Delivery roto],
    Archive.[Exact Repeat] , Archive.[Cores Due], Archive.[Cartons Due], Archive.[Ribbons Due], Archive.[Laminate Due], Archive.[Cleaning Cards Due], Archive.[Chip Due], Archive.[Tamarack], Archive.[Backer Die Due]
    Archive.[Quadrel] , Archive.[Quadrel No], Archive.[Quadrel Date], Archive.[completed quad hrs], Archive.[Remaining Hrs- Quadrel], Archive.[Calc Length Thru quardel], Archive.[Tamarack Machine No], Archive.[Tamarack date], Archive.[Remaining Hr-Tamarack], Archive.[Calc Length Thru tamarack]Archive.[completed tam hrs], Archive.[Roto Machine No],Archive.[Quad speed],Archive.[tam speed], Archive.[roto speed], Archive.[Feet Per Core tam] .[Feet Per Core quad], Archive.[Offline Delivery tam],Archive.[Offline Delivery quad],Archive.[No Wide Thru quad],Archive.[No Wide Thru Tam], Archive.[Special Pallets Due],Archive.[Security Tape Due],Archive.[Content Number], Archive.[Plate Length], Archive.[Number Wide Plates] FROM Archive WHERE [Order Number] = '" & Me![Combo970] & "' " 'Me![Combo970]"
    Set MyDb = CurrentDb

[westconn1]

there would be 2 ways. append or line contiuation

sql = "select * from mytable"
sql = sql & " where id = 1234"

'OR

strsql = "select * from mytable" & _
" where id = 1234"

in either, make sure to allow for spaces where required

[cubsm22p]

ok one more question about the combo box
i have two combo boxes how do i create a vba code to make combo970 to have the same ordernumber as combo960

[westconn1]

you can try like

Private Sub ComboBox1_Click()
ComboBox2.Value = ComboBox1.Text
End Sub

change combo box names to suit

[cubsm22p]

i tried Combo970.Value = Combo960.Text and got this error message how do i set focus back on the form

[westconn1]

how do i set focus back on the form

i did test using a userform with 2 combo boxes before posting, so i expected it to work, i can not test with your layout and data

[cubsm22p]

i used this code instead to get the current record on to the form

 DoCmd.OpenForm "Order Master", acNormal, , "[Order Number] = '" & Me![Combo970] & "'"
        Me.Refresh