[RESOLVED] Combobox and VBA help-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] Combobox and VBA help

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Resolved [RESOLVED] Combobox and VBA help

    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
    Code:
    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
    Name:  example.jpg
Views: 174
Size:  7.8 KB
    Last edited by cubsm22p; Feb 2nd, 2018 at 03:50 PM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,273

    Re: Combobox and VBA help

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Re: Combobox and VBA help

    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

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Re: Combobox and VBA help

    i tried what u said it ran with out any error codes, but the order master table isnt being populated with any new feilds
    Code:
    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Re: Combobox and VBA help

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

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Re: Combobox and VBA help

    One more question how do i use multiple lines with sql in vba EXAMPLE:
    Code:
    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

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,273

    Re: Combobox and VBA help

    there would be 2 ways. append or line contiuation

    Code:
    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
    Last edited by westconn1; Feb 2nd, 2018 at 09:42 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Re: Combobox and VBA help

    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
    Name:  combobox-vbforum.jpg
Views: 38
Size:  13.0 KB

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,273

    Re: Combobox and VBA help

    you can try like
    Code:
    Private Sub ComboBox1_Click()
    ComboBox2.Value = ComboBox1.Text
    End Sub
    change combo box names to suit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Re: Combobox and VBA help

    Name:  erroronmsg.jpg
Views: 33
Size:  23.5 KB
    i tried Combo970.Value = Combo960.Text and got this error message how do i set focus back on the form

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,273

    Re: Combobox and VBA help

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    23

    Re: Combobox and VBA help

    i used this code instead to get the current record on to the form
    Code:
     DoCmd.OpenForm "Order Master", acNormal, , "[Order Number] = '" & Me![Combo970] & "'"
            Me.Refresh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width