-
Feb 2nd, 2018, 03:24 PM
#1
Thread Starter
Member
[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
Last edited by cubsm22p; Feb 2nd, 2018 at 03:50 PM.
-
Feb 2nd, 2018, 04:23 PM
#2
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
-
Feb 2nd, 2018, 04:51 PM
#3
Thread Starter
Member
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
-
Feb 2nd, 2018, 04:55 PM
#4
Thread Starter
Member
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
-
Feb 2nd, 2018, 05:15 PM
#5
Thread Starter
Member
Re: Combobox and VBA help
Hey Thanks alot just got it had to deal wil nulls using Nz()
-
Feb 2nd, 2018, 06:11 PM
#6
Thread Starter
Member
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
-
Feb 2nd, 2018, 09:32 PM
#7
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
-
Feb 5th, 2018, 05:31 PM
#8
Thread Starter
Member
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
-
Feb 6th, 2018, 03:57 AM
#9
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
-
Feb 6th, 2018, 10:58 AM
#10
Thread Starter
Member
Re: Combobox and VBA help
i tried Combo970.Value = Combo960.Text and got this error message how do i set focus back on the form
-
Feb 6th, 2018, 03:15 PM
#11
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
-
Feb 12th, 2018, 03:02 PM
#12
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|