473,387 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > microsoft access / vba > questions > capture a user input on a form in vba

Join Bytes to post your question to a community of 473,387 software developers and data experts.

capture a user input on a form in VBA

I need to capture user input after user clicks append query button and types in an order number. with this captured il use the variable to compare it to the delete from archeive query
Jan 22 '18 #1
35 3677
PhilOfWalton
1,430 Expert 1GB
Your question is far from clear.

It sounds as if you are trying to find a record from a table or query.
Could you rephrase the question and tell us what you are trying to achieve, rather than the detail about capture & buttons.

Phil
Jan 23 '18 #2
if i could find a way to post a screen shot i think that would help, if i had a guess i would say the setfocus methodmight help
Jan 23 '18 #3
PhilOfWalton
1,430 Expert 1GB
Use PrintScreen to capture your screen.

Use Paint to crop it to the relevant area. Shrink it to about 3" wide and 2" high. Is it still easy to read?

Then on this website use the Advanced Tab ->> Insert --> Manage Attachmente to select the image. Select Upload

If you double click on the image, it will show something like
https://bytes.com/attachment.php?att...1&d=1516724952 as it's addtress.

Copy that address and on this website, click the Inset Image (Yellow) symbol.

Paste the address into that.

Good luck

Phil
Jan 23 '18 #4
i think i have it as an attachment circled in blue is what im trying to get to compare values

Attached Images
File Type: jpg questionofvariable.jpg (76.6 KB, 518 views)
Jan 23 '18 #5
this is the vba code associated with the button circled up top in blue

Attached Images
File Type: jpg image.jpg (55.7 KB, 491 views)
Jan 23 '18 #6
PhilOfWalton
1,430 Expert 1GB
So are you basically saying you want to select an existing order and use it as a "Template" for a new order?

Phil
Jan 24 '18 #7
yea i would like to manupulate that user entry from that append query text box in my VBA code
Jan 24 '18 #8
PhilOfWalton
1,430 Expert 1GB
So first, don't use an input box to find an order, use a Combo Box to select an existing order as the template.

So briefly, on the AfterUpdate of the Combo,
1)ind the order
In your VBA have variable corresponding to the fields that you want to use in your template (it almost certainly will not be all of them)
Jan 24 '18 #9
PhilOfWalton
1,430 Expert 1GB
Sorry, if you hit a tab key the message gets sent, Bad website design, certainly not going to blame myself for hitting the wrong key.

1) Find the order to use as a template.
2) In your VBA have variable corresponding to the fields that you want to use in your template (it almost certainly will not be all of them)
3) Save those fields from the Template Record to your variables
4) Use the AddNew to go to a new record
5) Load the variables back to the required fields in your new record
6) Update the new record

Hope that helps

Phil
Jan 24 '18 #10
would it be hard to use a drop box if i have thousands of records for that feild
Jan 25 '18 #11
NeoPa
32,556 Expert Mod 16PB
BWCooper:
this is the vba code associated with the button circled up top in blue
Please don't post code as a picture! We have [CODE] tags that allow you to post the code so that it can be worked with more easily. A picture is wasteful of disk space and little use to anyone trying to help you.
Jan 25 '18 #12
PhilOfWalton
1,430 Expert 1GB
Sorry, "thousands of records for that feild" makes no sense to me, even if your post should have been "thousands of records for that field".
A field is an item of data within a record.

What has DropBox got to do with anything?

Phil
Jan 25 '18 #13
Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_Repeat_Order_Click()
  2. On Error GoTo Err_Add_Repeat_Order_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stValue   As String
  6.  
  7.     stDocName = "Add Repeat Order"
  8.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  9.  
  10.    ' DoCmd.GoToRecord
  11.  
  12.     stValue = Forms![Order Master]![Add Repeat Order]!Value
  13.     MsgBox "value = " + stValue
  14.  
  15. Exit_Add_Repeat_Order_Click:
  16.     Exit Sub
  17.  
  18. Err_Add_Repeat_Order_Click:
  19.     MsgBox Err.Description
  20.     Resume Exit_Add_Repeat_Order_Click
  21.  
  22. End Sub
Jan 26 '18 #14
NeoPa
32,556 Expert Mod 16PB
Well done. I didn't expect that retrospectively but it's good that you've done it.

Now I would suggest you re-engage with Phil's last post and help him understand where you're currently at and what you still need.

Good luck :-)
Jan 27 '18 #15
Ok i have taken your advise and going for the combobox idea, i have created a combo box to look up OrderNumber in the ARCHIVE table when i do this i want to take the record and feilds assosiated with it and appened the record to the Order Master table also at the same time i want the same record typed in to the combo box to populate the feilds in the form

NOTE: the record source for the form is Order Master Table and the record source for the combo button is Archive Table


Some code i guessed on for the combobox
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo963_AfterUpdate()
  2.  
  3.  Dim rs As Object
  4.  
  5.     Set rs = Me.Recordset.Clone
  6.     rs.FindFirst "[ID] = " & Str(Nz(Me![Combo963], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8.  
  9. Dim dbs As Database
  10. Set dbs = OpenDatabase("ALVIN_7.mdb")
  11. dbs.Execute "INSERT INTO Order Master" & "select *" & "FROM Archive" & "WHERE Table![Archeive]![Order Number] Equal rs"
  12. End Sub
Jan 30 '18 #16
Keeps giving me error on sql INSERT INTO statement

Attached Images
File Type: jpg wef.jpg (22.1 KB, 375 views)
Jan 30 '18 #17
PhilOfWalton
1,430 Expert 1GB
Not the way I would so let's get back to first principles.

On your Archive table, do you have an OrderID (AutoNumber) an Order No and a Customer (The customer should be in a separate table and there should be a CustomerID in the Archive table.

So the Combo Box should have the ControlSorce based on a query that shows the 3 fields above (or whatever is easiest to find the "template".
Assuming Column 1 is the bound column and bound to the order ID having selected it, you need something like this (Air code and unchecked)

Incidently Combo963 isn't very helpful, so I have changed it to CboFindArchive

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboFindArchive_AfterUpdate()
  2.  
  3.         Dim MyDb as database
  4.     Dim ArchiveSet as RecordSet
  5.     Dim CurrentSet as RecordSet
  6.     Dim StrSQL as String
  7.     ' The fields below represent the fields you want to add to your new record
  8.     ' So Name them appropriately and give them the correct datatypes
  9.     Dim Save1 as String
  10.         Dim Save2 as Long
  11.         Dim Save3 as Long
  12.         Dim Save5 as String
  13.     '... etc
  14.  
  15.     StrSQL = "SELECT TblArchive.* FROM TblArhive WHERE OrderID = " & CboFindArchive & ";"
  16.  
  17.     Set MyDb = CurrentDb
  18.  
  19.     ' This opens the single "Template Record"
  20.     Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
  21.  
  22.     With ArchiveSet
  23.         Save1 = !OrderNo
  24.                 Save2 = !CustomerID
  25.         Save3 = !JobTypeID
  26.         Save4 = !DeliveryMethod
  27.         ' ... etc
  28.         .Close
  29.         Set ArchiveSet = Nothing
  30.     End with
  31.  
  32.     ' So we have captured all the data we want from the "Template"
  33.     ' Now create the new record in the Current Table
  34.     ' Assume that the field names are the same in both tables
  35.  
  36.     StrSQL = "SELECT TblCurrent.* FROM TblCurrent;"
  37.  
  38.     Set CurrentSet = MyDb.OpenRecordset(StrSQL)
  39.  
  40.     With CurrentSet
  41.         .AddNew
  42.         !OrderNo = Save1
  43.                 !CustomerID = Save2 
  44.         !JobTypeID = Save3 
  45.         DeliveryMethod = Save4 
  46.         ' ... etc
  47.         .Update
  48.         .Close
  49.         Set CurrentSet = Nothing
  50.     End with
  51.  
  52.     ' So Now we have a new record in the table, its a matter of getting to it on the form
  53.  
  54.     Me.Requert
  55.          DoCmd.GotoRecord acLast
  56.  
  57. End Sub
  58.  
See how you get on

Phil
Jan 30 '18 #18
ok makes sense is there an easier way to get all the fields on the the table here are my fields names in an attachment
Attached Images
File Type: png feildNames.PNG (72.0 KB, 105 views)
File Type: png feildName1.PNG (81.9 KB, 123 views)
File Type: png FeildNames3.PNG (86.4 KB, 170 views)
File Type: png feild3.PNG (26.7 KB, 74 views)
Jan 31 '18 #19
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo963_AfterUpdate()
  2.  
  3. Dim MyDb As Database
  4.     Dim ArchiveSet As Recordset
  5.     Dim CurrentSet As Recordset
  6.     Dim StrSQL As String
  7.     ' The fields below represent the fields you want to add to your new record
  8.     ' So Name them appropriately and give them the correct datatypes
  9. Dim Save2 As String
  10. Dim Save3 As String
  11. Dim Save4 As String
  12. Dim Save5 As String
  13. Dim Save6 As String
  14. Dim Save7 As String
  15. Dim Save8 As String
  16. Dim Save9 As String
  17. Dim Save10 As String
  18. Dim Save11 As String
  19. Dim Save12 As String
  20. Dim Save13 As String
  21. Dim Save14 As String
  22. Dim Save15 As String
  23. Dim Save16 As String
  24. Dim Save17 As String
  25. Dim Save18 As String
  26. Dim Save19 As String
  27. Dim Save20 As String
  28. Dim Save21 As String
  29. Dim Save22 As String
  30. Dim Save23 As String
  31. Dim Save24 As String
  32. Dim Save25 As String
  33. Dim Save26 As String
  34. Dim Save27 As String
  35. Dim Save28 As String
  36. Dim Save29 As String
  37. Dim Save30 As String
  38. Dim Save31 As String
  39. Dim Save32 As String
  40. Dim Save33 As String
  41. Dim Save34 As String
  42. Dim Save35 As String
  43. Dim Save36 As String
  44. Dim Save37 As String
  45. Dim Save38 As String
  46. Dim Save39 As String
  47. Dim Save40 As String
  48. Dim Save41 As String
  49. Dim Save42 As String
  50. Dim Save43 As String
  51. Dim Save44 As String
  52. Dim Save45 As String
  53. Dim Save46 As String
  54. Dim Save47 As String
  55. Dim Save48 As String
  56. Dim Save49 As String
  57. Dim Save50 As String
  58. Dim Save51 As String
  59. Dim Save52 As String
  60. Dim Save53 As String
  61. Dim Save54 As String
  62. Dim Save55 As String
  63. Dim Save56 As String
  64. Dim Save57 As String
  65. Dim Save58 As String
  66. Dim Save59 As String
  67. Dim Save60 As String
  68. Dim Save61 As String
  69. Dim Save62 As String
  70. Dim Save63 As String
  71. Dim Save64 As String
  72. Dim Save65 As String
  73. Dim Save66 As String
  74. Dim Save67 As String
  75. Dim Save68 As String
  76. Dim Save69 As String
  77. Dim Save70 As String
  78. Dim Save71 As String
  79. Dim Save72 As String
  80. Dim Save73 As String
  81. Dim Save74 As String
  82. Dim Save75 As String
  83. Dim Save76 As String
  84. Dim Save77 As String
  85. Dim Save78 As String
  86. Dim Save79 As String
  87. Dim Save80 As String
  88. Dim Save81 As String
  89. Dim Save82 As String
  90. Dim Save83 As String
  91. Dim Save84 As String
  92. Dim Save85 As String
  93. Dim Save86 As String
  94. Dim Save87 As String
  95. Dim Save88 As String
  96. Dim Save89 As String
  97. Dim Save90 As String
  98. Dim Save91 As String
  99. Dim Save92 As String
  100. Dim Save93 As String
  101. Dim Save94 As String
  102. Dim Save95 As String
  103. Dim Save96 As String
  104. Dim Save97 As String
  105. Dim Save98 As String
  106.     StrSQL = "SELECT TblArchive.* FROM TblArhive WHERE OrderID = " & Combo963 & ";"
  107.  
  108.     Set MyDb = CurrentDb
  109.  
  110.     ' This opens the single "Template Record"
  111.     Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
  112.  
  113.     With ArchiveSet
  114. Save2 = ![Order Number]
  115. Save3 = ![Date Entered Into Alvin]
  116. Save4 = ![Customer]
  117. Save5 = ![Received]
  118. Save6 = ![Need By]
  119. Save7 = ![Sales Rep]
  120. Save8 = ![Parts]
  121. Save9 = ![Scheduled Ship]
  122. Save10 = ![Width]
  123. Save11 = ![Length]
  124. Save12 = ![Quantity]
  125. Save13 = ![Delivery Method]
  126. Save14 = ![Midax]
  127. Save15 = ![WCSS]
  128. Save16 = ![Job Type]
  129. Save17 = ![Why]
  130. Save18 = ![Service Rep]
  131. Save19 = ![Why Reop]
  132. Save20 = ![Plate Date]
  133. Save21 = ![Press Date]
  134. Save22 = ![Press Sequence]
  135. Save23 = ![Collator Date]
  136. Save24 = ![Offline Date]
  137. Save25 = ![Paper Due]
  138. Save26 = ![Carbon Due]
  139. Save27 = ![Die Due]
  140. Save28 = ![Ink Due]
  141. Save29 = ![Repeat Order Numbers]
  142. Save30 = ![Misc Due]
  143. Save31 = ![Companion Order]
  144. Save32 = ![Companion Order Number]
  145. Save33 = ![Press]
  146. Save34 = ![No Parts]
  147. Save35 = ![No Wide]
  148. Save36 = ![Calc Length]
  149. Save37 = ![Speed]
  150. Save38 = ![Calc Hrs]
  151. Save39 = ![Completed]
  152. Save40 = ![Remaining]
  153. Save41 = ![Additional Hrs]
  154. Save42 = ![No of Inks Face]
  155. Save43 = ![No of Inks Back]
  156. Save44 = ![Die Number(s) Face]
  157. Save45 = ![Die Number(s) Liner]
  158. Save46 = ![Collator No:]
  159. Save47 = ![Calc Collator Hours]
  160. Save48 = ![CompletedC]
  161. Save49 = ![RemainingC]
  162. Save50 = ![Collator Delivery]
  163. Save51 = ![Offline Machine No]
  164. Save52 = ![Calc roto Hrs]
  165. Save53 = ![Feet Per Core roto]
  166. Save54 = ![Order Complete]
  167. Save55 = ![Collator Speed]
  168. Save56 = ![Offline Speed]
  169. Save57 = ![No Wide Thru Collator]
  170. Save58 = ![Calc Length Thru Collator]
  171. Save59 = ![No Wide Thru Roto]
  172. Save60 = ![Calc Length Thru roto]
  173. Save61 = ![Completed roto Hrs]
  174. Save62 = ![Remaining roto Hrs]
  175. Save63 = ![Offline Delivery roto]
  176. Save64 = ![Exact Repeat]
  177. Save65 = ![Cores Due]
  178. Save66 = ![Cartons Due]
  179. Save67 = ![Ribbons Due]
  180. Save68 = ![Laminate Due]
  181. Save69 = ![Cleaning Cards Due]
  182. Save70 = ![Chip Due]
  183. Save71 = ![Tamarack]
  184. Save72 = ![Backer Die Due]
  185. Save73 = ![Quadrel]
  186. Save74 = ![Quadrel No]
  187. Save75 = ![Quadrel Date]
  188. Save76 = ![completed quad hrs]
  189. Save77 = ![Remaining Hrs- Quadrel]
  190. Save78 = ![Calc Length Thru quardel]
  191. Save79 = ![Tamarack Machine No]
  192. Save80 = ![Tamarack date]
  193. Save81 = ![Remaining Hr-Tamarack]
  194. Save82 = ![Calc Length Thru tamarack]
  195. Save83 = ![completed tam hrs]
  196. Save84 = ![Roto Machine No]
  197. Save85 = ![Quad speed]
  198. Save86 = ![tam speed]
  199. Save87 = ![roto speed]
  200. Save88 = ![Feet Per Core tam]
  201. Save89 = ![Feet Per Core quad]
  202. Save90 = ![Offline Delivery tam]
  203. Save91 = ![Offline Delivery quad]
  204. Save92 = ![No Wide Thru quad]
  205. Save93 = ![No Wide Thru Tam]
  206. Save94 = ![Special Pallets Due]
  207. Save95 = ![Security Tape Due]
  208. Save96 = ![Content Number]
  209. Save97 = ![Plate Length]
  210. Save98 = ![Number Wide Plates]
  211.         .Close
  212.         Set ArchiveSet = Nothing
  213.     End With
  214.  
  215.     ' So we have captured all the data we want from the "Template"
  216.     ' Now create the new record in the Current Table
  217.     ' Assume that the field names are the same in both tables
  218.  
  219.     StrSQL = "SELECT TblOrder Master.* FROM TblOrder Master;"
  220.  
  221.     Set CurrentSet = MyDb.OpenRecordset(StrSQL)
  222.  
  223.     With CurrentSet
  224.         .AddNew
  225.         ![Order Number] = Save2
  226. ![Date Entered Into Alvin] = Save3
  227. ![Customer] = Save4
  228. ![Received] = Save5
  229. ![Need By] = Save6
  230. ![Sales Rep] = Save7
  231. ![Parts] = Save8
  232. ![Scheduled Ship] = Save9
  233. ![Width] = Save10
  234. ![Length] = Save11
  235. ![Quantity] = Save12
  236. ![Delivery Method] = Save13
  237. ![Midax] = Save14
  238. ![WCSS] = Save15
  239. ![Job Type] = Save16
  240. ![Why] = Save17
  241. ![Service Rep] = Save18
  242. ![Why Reop] = Save19
  243. ![Plate Date] = Save20
  244. ![Press Date] = Save21
  245. ![Press Sequence] = Save22
  246. ![Collator Date] = Save23
  247. ![Offline Date] = Save24
  248. ![Paper Due] = Save25
  249. ![Carbon Due] = Save26
  250. ![Die Due] = Save27
  251. ![Ink Due] = Save28
  252. ![Repeat Order Numbers] = Save29
  253. ![Misc Due] = Save30
  254. ![Companion Order] = Save31
  255. ![Companion Order Number] = Save32
  256. ![Press] = Save33
  257. ![No Parts] = Save34
  258. ![No Wide] = Save35
  259. ![Calc Length] = Save36
  260. ![Speed] = Save37
  261. ![Calc Hrs] = Save38
  262. ![Completed] = Save39
  263. ![Remaining] = Save40
  264. ![Additional Hrs] = Save41
  265. ![No of Inks Face] = Save42
  266. ![No of Inks Back] = Save43
  267. ![Die Number(s) Face] = Save44
  268. ![Die Number(s) Liner] = Save45
  269. ![Collator No:] = Save46
  270. ![Calc Collator Hours] = Save47
  271. ![CompletedC] = Save48
  272. ![RemainingC] = Save49
  273. ![Collator Delivery] = Save50
  274. ![Offline Machine No] = Save51
  275. ![Calc roto Hrs] = Save52
  276. ![Feet Per Core roto] = Save53
  277. ![Order Complete] = Save54
  278. ![Collator Speed] = Save55
  279. ![Offline Speed] = Save56
  280. ![No Wide Thru Collator] = Save57
  281. ![Calc Length Thru Collator] = Save58
  282. ![No Wide Thru Roto] = Save59
  283. ![Calc Length Thru roto] = Save60
  284. ![Completed roto Hrs] = Save61
  285. ![Remaining roto Hrs] = Save62
  286. ![Offline Delivery roto] = Save63
  287. ![Exact Repeat] = Save64
  288. ![Cores Due] = Save65
  289. ![Cartons Due] = Save66
  290. ![Ribbons Due] = Save67
  291. ![Laminate Due] = Save68
  292. ![Cleaning Cards Due] = Save69
  293. ![Chip Due] = Save70
  294. ![Tamarack] = Save71
  295. ![Backer Die Due] = Save72
  296. ![Quadrel] = Save73
  297. ![Quadrel No] = Save74
  298. ![Quadrel Date] = Save75
  299. ![completed quad hrs] = Save76
  300. ![Remaining Hrs- Quadrel] = Save77
  301. ![Calc Length Thru quardel] = Save78
  302. ![Tamarack Machine No] = Save79
  303. ![Tamarack date] = Save80
  304. ![Remaining Hr-Tamarack] = Save81
  305. ![Calc Length Thru tamarack] = Save82
  306. ![completed tam hrs] = Save83
  307. ![Roto Machine No] = Save84
  308. ![Quad speed] = Save85
  309. ![tam speed] = Save86
  310. ![roto speed] = Save87
  311. ![Feet Per Core tam] = Save88
  312. ![Feet Per Core quad] = Save89
  313. ![Offline Delivery tam] = Save90
  314. ![Offline Delivery quad] = Save91
  315. ![No Wide Thru quad] = Save92
  316. ![No Wide Thru Tam] = Save93
  317. ![Special Pallets Due] = Save94
  318. ![Security Tape Due] = Save95
  319. ![Content Number] = Save96
  320. ![Plate Length] = Save97
  321. ![Number Wide Plates] = Save98
  322.         .Update
  323.         .Close
  324.         Set CurrentSet = Nothing
  325.     End With
  326.  
  327.     ' So Now we have a new record in the table, its a matter of getting to it on the form
  328.  
  329.     Me.Requert
  330.          DoCmd.GoToRecord acLast
  331.  
  332. ' Dim rs As Object
  333.  
  334. '    Set rs = Me.Recordset.Clone
  335.  '   rs.FindFirst "[ID] = " & Str(Nz(Me![Combo963], 0))
  336.   '  If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  337.  
Jan 31 '18 #20
This strSQL does not work
Expand|Select|Wrap|Line Numbers
  1. StrSQL = "SELECT Archive.* FROM Archive WHERE [Order Number] = " & Combo963 & ";"
  2.  
  3.     Set MyDb = CurrentDb
  4.  
  5.     ' This opens the single "Template Record"
  6.     Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
Jan 31 '18 #21
Red is the combobox that i selected "find a record on my form based on the value i selected in my combo box"
and when i select an order number with this red combobox it populates the feilds on the form,

but the blue combo box i selected "i want the combobox to look up the values in a table or query" then linked the bluecombo box to the ARCHEIVE table. i want this blue combobox to act like the red.

what i want it to do
>user selects order number in blue combo box
>program takes record that matches with the order number and add the record to the Order Master Table
>then automatically update the red combobox to show the new record so the form gets populated with this new record
Attached Images
File Type: jpg example of daa.jpg (55.7 KB, 126 views)
Jan 31 '18 #22
PhilOfWalton
1,430 Expert 1GB
You don't say what is going wrong.

What precisely is is the RowSource of Combo963?
You have defined all your Save Fields as strings, then tried to load numbers and dates into those fields.

In the example I gave you I was careful to define different data types to match the data types in your records.

Let's get one thing at a time correct. There is no point in worrying about the form till you can see the new record in the table.

Phil
Jan 31 '18 #23
this is the new and approved code with updated data types
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo963_AfterUpdate()
  2.  
  3. Dim MyDb As Database
  4.     Dim ArchiveSet As Recordset
  5.     Dim CurrentSet As Recordset
  6.     Dim StrSQL As String
  7.     ' The fields below represent the fields you want to add to your new record
  8.     ' So Name them appropriately and give them the correct datatypes
  9. Dim Save2 As String
  10. Dim Save3 As Date
  11. Dim Save4 As String
  12. Dim Save5 As Date
  13. Dim Save6 As Date
  14. Dim Save7 As String
  15. Dim Save8 As String
  16. Dim Save9 As Date
  17. Dim Save10 As Integer
  18. Dim Save11 As Integer
  19. Dim Save12 As Integer
  20. Dim Save13 As Integer
  21. Dim Save14 As Boolean
  22. Dim Save15 As String
  23. Dim Save16 As String
  24. Dim Save17 As String
  25. Dim Save18 As String
  26. Dim Save19 As String
  27. Dim Save20 As Date
  28. Dim Save21 As Date
  29. Dim Save22 As Integer
  30. Dim Save23 As Date
  31. Dim Save24 As Date
  32. Dim Save25 As Date
  33. Dim Save26 As Date
  34. Dim Save27 As Date
  35. Dim Save28 As Date
  36. Dim Save29 As String
  37. Dim Save30 As Date
  38. Dim Save31 As Boolean
  39. Dim Save32 As String
  40. Dim Save33 As Integer
  41. Dim Save34 As String
  42. Dim Save35 As Integer
  43. Dim Save36 As Integer
  44. Dim Save37 As Integer
  45. Dim Save38 As Integer
  46. Dim Save39 As Integer
  47. Dim Save40 As Integer
  48. Dim Save41 As Integer
  49. Dim Save42 As Integer
  50. Dim Save43 As Integer
  51. Dim Save44 As String
  52. Dim Save45 As String
  53. Dim Save46 As Integer
  54. Dim Save47 As Integer
  55. Dim Save48 As Integer
  56. Dim Save49 As Integer
  57. Dim Save50 As String
  58. Dim Save51 As Integer
  59. Dim Save52 As Integer
  60. Dim Save53 As String
  61. Dim Save54 As Boolean
  62. Dim Save55 As Integer
  63. Dim Save56 As Integer
  64. Dim Save57 As Integer
  65. Dim Save58 As Integer
  66. Dim Save59 As String
  67. Dim Save60 As Integer
  68. Dim Save61 As Integer
  69. Dim Save62 As Integer
  70. Dim Save63 As String
  71. Dim Save64 As Boolean
  72. Dim Save65 As Date
  73. Dim Save66 As Date
  74. Dim Save67 As Date
  75. Dim Save68 As Date
  76. Dim Save69 As Date
  77. Dim Save70 As Date
  78. Dim Save71 As Boolean
  79. Dim Save72 As Date
  80. Dim Save73 As Boolean
  81. Dim Save74 As Integer
  82. Dim Save75 As Date
  83. Dim Save76 As Integer
  84. Dim Save77 As Integer
  85. Dim Save78 As Integer
  86. Dim Save79 As Integer
  87. Dim Save80 As Date
  88. Dim Save81 As Integer
  89. Dim Save82 As Integer
  90. Dim Save83 As Integer
  91. Dim Save84 As Integer
  92. Dim Save85 As String
  93. Dim Save86 As Integer
  94. Dim Save87 As Integer
  95. Dim Save88 As String
  96. Dim Save89 As String
  97. Dim Save90 As String
  98. Dim Save91 As String
  99. Dim Save92 As String
  100. Dim Save93 As String
  101. Dim Save94 As String
  102. Dim Save95 As String
  103. Dim Save96 As String
  104. Dim Save97 As String
  105. Dim Save98 As String
  106.     StrSQL = "SELECT * FROM Archive WHERE [Order Number] = " & Combo963 & ";"
  107.  
  108.     Set MyDb = CurrentDb
  109.  
  110.     ' This opens the single "Template Record"
  111.     Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
  112.  
  113.     With ArchiveSet
  114. Save2 = ![Order Number]
  115. Save3 = ![Date Entered Into Alvin]
  116. Save4 = ![Customer]
  117. Save5 = ![Received]
  118. Save6 = ![Need By]
  119. Save7 = ![Sales Rep]
  120. Save8 = ![Parts]
  121. Save9 = ![Scheduled Ship]
  122. Save10 = ![Width]
  123. Save11 = ![Length]
  124. Save12 = ![Quantity]
  125. Save13 = ![Delivery Method]
  126. Save14 = ![Midax]
  127. Save15 = ![WCSS]
  128. Save16 = ![Job Type]
  129. Save17 = ![Why]
  130. Save18 = ![Service Rep]
  131. Save19 = ![Why Reop]
  132. Save20 = ![Plate Date]
  133. Save21 = ![Press Date]
  134. Save22 = ![Press Sequence]
  135. Save23 = ![Collator Date]
  136. Save24 = ![Offline Date]
  137. Save25 = ![Paper Due]
  138. Save26 = ![Carbon Due]
  139. Save27 = ![Die Due]
  140. Save28 = ![Ink Due]
  141. Save29 = ![Repeat Order Numbers]
  142. Save30 = ![Misc Due]
  143. Save31 = ![Companion Order]
  144. Save32 = ![Companion Order Number]
  145. Save33 = ![Press]
  146. Save34 = ![No Parts]
  147. Save35 = ![No Wide]
  148. Save36 = ![Calc Length]
  149. Save37 = ![Speed]
  150. Save38 = ![Calc Hrs]
  151. Save39 = ![Completed]
  152. Save40 = ![Remaining]
  153. Save41 = ![Additional Hrs]
  154. Save42 = ![No of Inks Face]
  155. Save43 = ![No of Inks Back]
  156. Save44 = ![Die Number(s) Face]
  157. Save45 = ![Die Number(s) Liner]
  158. Save46 = ![Collator No:]
  159. Save47 = ![Calc Collator Hours]
  160. Save48 = ![CompletedC]
  161. Save49 = ![RemainingC]
  162. Save50 = ![Collator Delivery]
  163. Save51 = ![Offline Machine No]
  164. Save52 = ![Calc roto Hrs]
  165. Save53 = ![Feet Per Core roto]
  166. Save54 = ![Order Complete]
  167. Save55 = ![Collator Speed]
  168. Save56 = ![Offline Speed]
  169. Save57 = ![No Wide Thru Collator]
  170. Save58 = ![Calc Length Thru Collator]
  171. Save59 = ![No Wide Thru Roto]
  172. Save60 = ![Calc Length Thru roto]
  173. Save61 = ![Completed roto Hrs]
  174. Save62 = ![Remaining roto Hrs]
  175. Save63 = ![Offline Delivery roto]
  176. Save64 = ![Exact Repeat]
  177. Save65 = ![Cores Due]
  178. Save66 = ![Cartons Due]
  179. Save67 = ![Ribbons Due]
  180. Save68 = ![Laminate Due]
  181. Save69 = ![Cleaning Cards Due]
  182. Save70 = ![Chip Due]
  183. Save71 = ![Tamarack]
  184. Save72 = ![Backer Die Due]
  185. Save73 = ![Quadrel]
  186. Save74 = ![Quadrel No]
  187. Save75 = ![Quadrel Date]
  188. Save76 = ![completed quad hrs]
  189. Save77 = ![Remaining Hrs- Quadrel]
  190. Save78 = ![Calc Length Thru quardel]
  191. Save79 = ![Tamarack Machine No]
  192. Save80 = ![Tamarack date]
  193. Save81 = ![Remaining Hr-Tamarack]
  194. Save82 = ![Calc Length Thru tamarack]
  195. Save83 = ![completed tam hrs]
  196. Save84 = ![Roto Machine No]
  197. Save85 = ![Quad speed]
  198. Save86 = ![tam speed]
  199. Save87 = ![roto speed]
  200. Save88 = ![Feet Per Core tam]
  201. Save89 = ![Feet Per Core quad]
  202. Save90 = ![Offline Delivery tam]
  203. Save91 = ![Offline Delivery quad]
  204. Save92 = ![No Wide Thru quad]
  205. Save93 = ![No Wide Thru Tam]
  206. Save94 = ![Special Pallets Due]
  207. Save95 = ![Security Tape Due]
  208. Save96 = ![Content Number]
  209. Save97 = ![Plate Length]
  210. Save98 = ![Number Wide Plates]
  211.         .Close
  212.         Set ArchiveSet = Nothing
  213.     End With
  214.  
  215.     ' So we have captured all the data we want from the "Template"
  216.     ' Now create the new record in the Current Table
  217.     ' Assume that the field names are the same in both tables
  218.  
  219.     StrSQL = "SELECT Order Master.* FROM Order Master;"
  220.  
  221.     Set CurrentSet = MyDb.OpenRecordset(StrSQL)
  222.  
  223.     With CurrentSet
  224.         .AddNew
  225.         ![Order Number] = Save2
  226. ![Date Entered Into Alvin] = Save3
  227. ![Customer] = Save4
  228. ![Received] = Save5
  229. ![Need By] = Save6
  230. ![Sales Rep] = Save7
  231. ![Parts] = Save8
  232. ![Scheduled Ship] = Save9
  233. ![Width] = Save10
  234. ![Length] = Save11
  235. ![Quantity] = Save12
  236. ![Delivery Method] = Save13
  237. ![Midax] = Save14
  238. ![WCSS] = Save15
  239. ![Job Type] = Save16
  240. ![Why] = Save17
  241. ![Service Rep] = Save18
  242. ![Why Reop] = Save19
  243. ![Plate Date] = Save20
  244. ![Press Date] = Save21
  245. ![Press Sequence] = Save22
  246. ![Collator Date] = Save23
  247. ![Offline Date] = Save24
  248. ![Paper Due] = Save25
  249. ![Carbon Due] = Save26
  250. ![Die Due] = Save27
  251. ![Ink Due] = Save28
  252. ![Repeat Order Numbers] = Save29
  253. ![Misc Due] = Save30
  254. ![Companion Order] = Save31
  255. ![Companion Order Number] = Save32
  256. ![Press] = Save33
  257. ![No Parts] = Save34
  258. ![No Wide] = Save35
  259. ![Calc Length] = Save36
  260. ![Speed] = Save37
  261. ![Calc Hrs] = Save38
  262. ![Completed] = Save39
  263. ![Remaining] = Save40
  264. ![Additional Hrs] = Save41
  265. ![No of Inks Face] = Save42
  266. ![No of Inks Back] = Save43
  267. ![Die Number(s) Face] = Save44
  268. ![Die Number(s) Liner] = Save45
  269. ![Collator No:] = Save46
  270. ![Calc Collator Hours] = Save47
  271. ![CompletedC] = Save48
  272. ![RemainingC] = Save49
  273. ![Collator Delivery] = Save50
  274. ![Offline Machine No] = Save51
  275. ![Calc roto Hrs] = Save52
  276. ![Feet Per Core roto] = Save53
  277. ![Order Complete] = Save54
  278. ![Collator Speed] = Save55
  279. ![Offline Speed] = Save56
  280. ![No Wide Thru Collator] = Save57
  281. ![Calc Length Thru Collator] = Save58
  282. ![No Wide Thru Roto] = Save59
  283. ![Calc Length Thru roto] = Save60
  284. ![Completed roto Hrs] = Save61
  285. ![Remaining roto Hrs] = Save62
  286. ![Offline Delivery roto] = Save63
  287. ![Exact Repeat] = Save64
  288. ![Cores Due] = Save65
  289. ![Cartons Due] = Save66
  290. ![Ribbons Due] = Save67
  291. ![Laminate Due] = Save68
  292. ![Cleaning Cards Due] = Save69
  293. ![Chip Due] = Save70
  294. ![Tamarack] = Save71
  295. ![Backer Die Due] = Save72
  296. ![Quadrel] = Save73
  297. ![Quadrel No] = Save74
  298. ![Quadrel Date] = Save75
  299. ![completed quad hrs] = Save76
  300. ![Remaining Hrs- Quadrel] = Save77
  301. ![Calc Length Thru quardel] = Save78
  302. ![Tamarack Machine No] = Save79
  303. ![Tamarack date] = Save80
  304. ![Remaining Hr-Tamarack] = Save81
  305. ![Calc Length Thru tamarack] = Save82
  306. ![completed tam hrs] = Save83
  307. ![Roto Machine No] = Save84
  308. ![Quad speed] = Save85
  309. ![tam speed] = Save86
  310. ![roto speed] = Save87
  311. ![Feet Per Core tam] = Save88
  312. ![Feet Per Core quad] = Save89
  313. ![Offline Delivery tam] = Save90
  314. ![Offline Delivery quad] = Save91
  315. ![No Wide Thru quad] = Save92
  316. ![No Wide Thru Tam] = Save93
  317. ![Special Pallets Due] = Save94
  318. ![Security Tape Due] = Save95
  319. ![Content Number] = Save96
  320. ![Plate Length] = Save97
  321. ![Number Wide Plates] = Save98
  322.         .Update
  323.         .Close
  324.         Set CurrentSet = Nothing
  325.     End With
  326.  
  327.     ' So Now we have a new record in the table, its a matter of getting to it on the form
  328.  
  329.     Me.Requery
  330.          DoCmd.GoToRecord acLast
  331.  
  332. ' Dim rs As Object
  333.  
  334. '    Set rs = Me.Recordset.Clone
  335.  '   rs.FindFirst "[ID] = " & Str(Nz(Me![Combo963], 0))
  336.   '  If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  337.  
  338. End Sub
Feb 1 '18 #24
here are some pictures of creating the combobox and a picture of the error code and where it says it is at ,

ps the row source for the combobox is the archive table the source for the form is the order master
Attached Images
File Type: jpg save1.jpg (63.3 KB, 119 views)
File Type: jpg save2.jpg (56.7 KB, 144 views)
File Type: jpg save3.jpg (56.5 KB, 137 views)
File Type: jpg save4.jpg (59.9 KB, 169 views)
File Type: jpg save7.jpg (28.4 KB, 139 views)
Feb 1 '18 #25
PhilOfWalton
1,430 Expert 1GB
Sorry, that doesn't help.

Against the RowSource for the Combo box you should have something that starts off
"SELECT .........." and probably should start
"SELECT ID, [Order Number], .......

When you get the error message, press the DEBUG Button, and that will show the line of code that the error occurred on. It almost certainly will be something like trying to load text into a numeric field or something similar.

Can you also check that all the numeric fields in your Archive table are type Integer. The Default is usually Long. So you may be trying to put a Long Data Type into an Integer Data Type

Phil
Feb 1 '18 #26
i think the error lies in here
Expand|Select|Wrap|Line Numbers
  1. StrSQL = "SELECT * FROM Archive WHERE [Order Number] = " & Combo963 & ";"
  2.  
  3.     Set MyDb = CurrentDb
  4.  
  5.     ' This opens the single "Template Record"
  6.     Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
Feb 1 '18 #27
ive been trying different methods like
Expand|Select|Wrap|Line Numbers
  1. 'Dim rs As Recordset
  2. 'Set rs = CurrentDb.OpenRecordset("SELECT .* FROM [TableName] WHERE CountNo = 5", dbOpenDynaset)
  3.  
  4.     'StrSQL = "SELECT Archive.* FROM Archive WHERE [Order Number] = rs"
  5.  Set CurrentSet = CurrentDb.OpenRecordset("SELECT Archive.* FROM [Archive] WHERE [Order Number] = me![Combo970] ", dbOpenDynaset)
  6.  
  7.     'Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
  8.  
  9.     ' This opens the single "Template Record"
Feb 1 '18 #28
ok when i use this code it works with out any errors but it isnt saving any of the records on the Order Master table after update
Expand|Select|Wrap|Line Numbers
  1.  StrSQL = "SELECT Archive.* FROM Archive "
  2.     Set MyDb = CurrentDb
  3.  
  4. ' This opens the single "Template Record"
  5.  Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
Feb 1 '18 #29
I got this to run and it did not put a record in the Order master table
Expand|Select|Wrap|Line Numbers
  1. StrSQL = "SELECT Archive.* FROM Archive WHERE [Order Number] = '084446714225' "
  2.     Set MyDb = CurrentDb
  3.  
  4. ' This opens the single "Template Record"
  5.  Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
  6.  
Feb 1 '18 #30
Ok got a code that ran an put a record into the order master table
here is the working code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo970_Change()
  2. Dim rs As Object
  3.  
  4.     Set rs = Me.Recordset.Clone
  5.      rs.FindFirst "[ID] = " & Str(Nz(Me![Combo970], 0))
  6.      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  7.  
  8. Dim MyDb As Database
  9.     Dim ArchiveSet As Recordset
  10.     Dim CurrentSet As Recordset
  11.     Dim StrSQL As String
  12.     ' The fields below represent the fields you want to add to your new record
  13.     ' So Name them appropriately and give them the correct datatypes
  14. Dim Save2 As String
  15.  
  16. Dim Save4 As String
  17.  
  18. Dim Save7 As String
  19. Dim Save8 As String
  20.  
  21.  
  22.     StrSQL = "SELECT Archive.[Order Number], Archive.[Customer], Archive.[Sales Rep], Archive.[Parts] FROM Archive WHERE [Order Number] = '084446714225'" 'Me![Combo970]"
  23.     Set MyDb = CurrentDb
  24.  
  25. ' This opens the single "Template Record"
  26.  Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
  27.  
  28.     With ArchiveSet
  29. Save2 = ![Order Number]
  30. 'Save3 = ![Date Entered Into Alvin]
  31. Save4 = ![Customer]
  32. 'Save5 = ![Received]
  33. 'Save6 = ![Need By]
  34. Save7 = ![Sales Rep]
  35. Save8 = ![Parts]
  36. 'Save9 = ![Scheduled Ship]
  37.         .Close
  38.         Set ArchiveSet = Nothing
  39.     End With
  40.  
  41.     ' So we have captured all the data we want from the "Template"
  42.     ' Now create the new record in the Current Table
  43.     ' Assume that the field names are the same in both tables
  44.  
  45.     StrSQL = "SELECT [Order Master].* FROM [Order Master];"
  46.  
  47.     Set CurrentSet = MyDb.OpenRecordset(StrSQL)
  48.  
  49.     With CurrentSet
  50.         .AddNew
  51.         ![Order Number] = Save2
  52. '![Date Entered Into Alvin] = Save3
  53. ![Customer] = Save4
  54. '![Received] = Save5
  55. '![Need By] = Save6
  56. ![Sales Rep] = Save7
  57. ![Parts] = Save8
  58. '![Scheduled Ship] = Save9
  59.  
  60.         .Update
  61.         .Close
  62.         Set CurrentSet = Nothing
  63.     End With
  64.  
  65.     ' So Now we have a new record in the table, its a matter of getting to it on the form
  66.  
  67.    ' Me.Requery
  68.     '     DoCmd.GoToRecord acLast
  69. End Sub
Feb 1 '18 #31
i beleve my problem is right here im tring to compare a string to an interger assuming the combobox value is integer
Expand|Select|Wrap|Line Numbers
  1. WHERE [Order Number] = cstr(Me![Combo970])"
Feb 1 '18 #32
PhilOfWalton
1,430 Expert 1GB
Sorry, I can't help unless you answer the questions I ask.

Phil
Feb 1 '18 #33
NeoPa
32,556 Expert Mod 16PB
May I suggest that you go back to the last post of Phil's and read it carefully. Find the last post where he asks for any information and read that carefully.

When you've done that prepare a response that ensures that any and all of his questions have been given as good answers as you can. Pay particular attention to the idea that the responses are appropriate to the questions.

With such an approach you will have much more luck getting solutions for your problems.
Feb 1 '18 #34
here is my full working code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo970_AfterUpdate()
  2. Dim rs As Object
  3.  
  4.    ' Set rs = Me.Recordset.Clone
  5.    ' rs.FindFirst "[ID] = " & Str(Nz(Me![Combo970], 0))
  6.    ' If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  7.  
  8.     Dim MyDb As Database
  9.     Dim ArchiveSet As Recordset
  10.     Dim CurrentSet As Recordset
  11.     Dim StrSQL As String
  12.     ' The fields below represent the fields you want to add to your new record
  13.     ' So Name them appropriately and give them the correct datatypes
  14.     Dim Save2 As String
  15. Dim Save4 As String
  16. Dim Save5 As Date
  17. Dim Save6 As Date
  18. Dim Save7 As String
  19. Dim Save8 As String
  20. Dim Save9 As Date
  21. Dim Save10 As Integer
  22. Dim Save11 As Integer
  23. Dim Save12 As Long
  24. Dim Save13 As Integer
  25. Dim Save14 As Boolean
  26. Dim Save15 As String
  27. Dim Save16 As String
  28. Dim Save17 As String
  29. Dim Save18 As String
  30. Dim Save19 As String
  31. Dim Save20 As Date
  32. Dim Save21 As Date
  33. Dim Save22 As Integer
  34. Dim Save23 As Date
  35. Dim Save24 As Date
  36. Dim Save25 As Date
  37. Dim Save26 As Date
  38. Dim Save27 As Date
  39. Dim Save28 As Date
  40. Dim Save29 As String
  41. Dim Save30 As Date
  42. Dim Save31 As Boolean
  43. Dim Save32 As String
  44. Dim Save33 As Integer
  45. Dim Save34 As String
  46. Dim Save35 As Integer
  47. Dim Save36 As Integer
  48. Dim Save37 As Integer
  49. Dim Save38 As Integer
  50. Dim Save39 As Integer
  51. Dim Save40 As Integer
  52. Dim Save41 As Integer
  53. Dim Save42 As Integer
  54. Dim Save43 As Integer
  55. Dim Save44 As String
  56. Dim Save45 As String
  57. Dim Save46 As Integer
  58. Dim Save47 As Integer
  59. Dim Save48 As Integer
  60. Dim Save49 As Integer
  61. Dim Save50 As String
  62. Dim Save51 As Integer
  63. Dim Save52 As Integer
  64. Dim Save53 As String
  65. Dim Save54 As Boolean
  66. Dim Save55 As Integer
  67. Dim Save56 As Integer
  68. Dim Save57 As Integer
  69. Dim Save58 As Integer
  70. Dim Save59 As String
  71. Dim Save60 As Integer
  72. Dim Save61 As Integer
  73. Dim Save62 As Integer
  74. Dim Save63 As String
  75. Dim Save64 As Boolean
  76. Dim Save65 As Date
  77. Dim Save66 As Date
  78. Dim Save67 As Date
  79. Dim Save68 As Date
  80. Dim Save69 As Date
  81. Dim Save70 As Date
  82. Dim Save71 As Boolean
  83. Dim Save72 As Date
  84. Dim Save73 As Boolean
  85. Dim Save74 As Integer
  86. Dim Save75 As Date
  87. Dim Save76 As Integer
  88. Dim Save77 As Integer
  89. Dim Save78 As Integer
  90. Dim Save79 As Integer
  91. Dim Save80 As Date
  92. Dim Save81 As Integer
  93. Dim Save82 As Integer
  94. Dim Save83 As Integer
  95. Dim Save84 As Integer
  96. Dim Save85 As Integer
  97. Dim Save86 As Integer
  98. Dim Save87 As Integer
  99. Dim Save88 As String
  100. Dim Save89 As String
  101. Dim Save90 As String
  102. Dim Save91 As String
  103. Dim Save92 As String
  104. Dim Save93 As String
  105. Dim Save94 As Date
  106. Dim Save95 As Date
  107. Dim Save96 As String
  108. Dim Save97 As String
  109. Dim Save98 As String
  110.  
  111.  
  112.     StrSQL = "SELECT Archive.[Order Number], Archive.[Customer],Archive.[Received],Archive.[Need By], Archive.[Sales Rep], Archive.[Parts]," & _
  113.     "[Archive].[Scheduled Ship],[Archive].[Width],[Archive].[Length],[Archive].[Quantity],[Archive].[Delivery Method],[Archive].[Midax],[Archive].[WCSS]," & _
  114.     "Archive.[Job Type],Archive.[Why],Archive.[Service Rep], Archive.[Why Reop], Archive.[Plate Date],Archive.[Press Date], Archive.[Press Sequence]," & _
  115.     "Archive.[Collator Date],Archive.[Offline Date], Archive.[Paper Due], Archive.[Carbon Due],Archive.[Die Due],Archive.[Ink Due]," & _
  116.     "Archive.[Repeat Order Numbers],Archive.[Misc Due],Archive.[Companion Order], Archive.[Companion Order Number], Archive.[Press],Archive.[No Parts]," & _
  117.     "Archive.[No Wide],Archive.[Calc Length],Archive.[Speed],Archive.[Calc Hrs], Archive.[Completed],Archive.[Remaining],Archive.[Additional Hrs]," & _
  118.     "Archive.[No of Inks Face],Archive.[No of Inks Back] , Archive.[Die Number(s) Face], Archive.[Die Number(s) Liner], Archive.[Collator No:]," & _
  119.     "Archive.[Calc Collator Hours] , Archive.[CompletedC],Archive.[RemainingC],Archive.[Collator Delivery], Archive.[Offline Machine No]," & _
  120.     "Archive.[Calc roto Hrs],Archive.[Feet Per Core roto], Archive.[Order Complete],Archive.[Collator Speed],Archive.[Offline Speed]," & _
  121.     "Archive.[No Wide Thru Collator],Archive.[Calc Length Thru Collator],Archive.[No Wide Thru Roto],Archive.[Calc Length Thru roto]," & _
  122.     "Archive.[Completed roto Hrs],Archive.[Remaining roto Hrs],Archive.[Offline Delivery roto],Archive.[Exact Repeat] , Archive.[Cores Due]," & _
  123.     "Archive.[Cartons Due], Archive.[Ribbons Due], Archive.[Laminate Due], Archive.[Cleaning Cards Due],Archive.[Chip Due] , Archive.[Tamarack]," & _
  124.     "Archive.[Backer Die Due] , Archive.[Quadrel], Archive.[Quadrel No], Archive.[Quadrel Date], Archive.[completed quad hrs]," & _
  125.     "Archive.[Remaining Hrs- Quadrel], Archive.[Calc Length Thru quardel], Archive.[Tamarack Machine No], Archive.[Tamarack date]," & _
  126.     "Archive.[Remaining Hr-Tamarack],Archive.[Calc Length Thru tamarack],Archive.[completed tam hrs], Archive.[Roto Machine No],Archive.[Quad speed]," & _
  127.     "Archive.[tam speed], Archive.[roto speed],Archive.[Feet Per Core Tam],Archive.[Feet Per Core quad], Archive.[Offline Delivery tam],Archive.[Offline Delivery quad]," & _
  128.     "Archive.[No Wide Thru quad],Archive.[No Wide Thru Tam],Archive.[Special Pallets Due],Archive.[Security Tape Due],Archive.[Content Number]," & _
  129.     "Archive.[Plate Length],Archive.[Number Wide Plates] FROM Archive WHERE [Order Number] = '" & Me![Combo970] & "' " 'Me![Combo970]"
  130.     Set MyDb = CurrentDb
  131.  
  132. ' This opens the single "Template Record"
  133.     Set ArchiveSet = MyDb.OpenRecordset(StrSQL)
  134.  
  135.     With ArchiveSet
  136. Save2 = Nz(![Order Number])
  137. Save4 = Nz(![Customer])
  138. Save5 = Nz(![Received])
  139. Save6 = Nz(![Need By])
  140. Save7 = Nz(![Sales Rep])
  141. Save8 = Nz(![Parts])
  142. Save9 = Nz(![Scheduled Ship])
  143. Save10 = Nz(![Width])
  144. Save11 = Nz(![Length])
  145. Save12 = Nz(![Quantity])
  146. Save13 = Nz(![Delivery Method])
  147. Save14 = Nz(![Midax])
  148. Save15 = Nz(![WCSS])
  149. Save16 = Nz(![Job Type])
  150. Save17 = Nz(![Why])
  151. Save18 = Nz(![Service Rep])
  152. Save19 = Nz(![Why Reop])
  153. Save20 = Nz(![Plate Date])
  154. Save21 = Nz(![Press Date])
  155. Save22 = Nz(![Press Sequence])
  156. Save23 = Nz(![Collator Date])
  157. Save24 = Nz(![Offline Date])
  158. Save25 = Nz(![Paper Due])
  159. Save26 = Nz(![Carbon Due])
  160. Save27 = Nz(![Die Due])
  161. Save28 = Nz(![Ink Due])
  162. Save29 = Nz(![Repeat Order Numbers])
  163. Save30 = Nz(![Misc Due])
  164. Save31 = Nz(![Companion Order])
  165. Save32 = Nz(![Companion Order Number])
  166. Save33 = Nz(![Press])
  167. Save34 = Nz(![No Parts])
  168. Save35 = Nz(![No Wide])
  169. Save36 = Nz(![Calc Length])
  170. Save37 = Nz(![Speed])
  171. Save38 = Nz(![Calc Hrs])
  172. Save39 = Nz(![Completed])
  173. Save40 = Nz(![Remaining])
  174. Save41 = Nz(![Additional Hrs])
  175. Save42 = Nz(![No of Inks Face])
  176. Save43 = Nz(![No of Inks Back])
  177. Save44 = Nz(![Die Number(s) Face])
  178. Save45 = Nz(![Die Number(s) Liner])
  179. Save46 = Nz(![Collator No:])
  180. Save47 = Nz(![Calc Collator Hours])
  181. Save48 = Nz(![CompletedC])
  182. Save49 = Nz(![RemainingC])
  183. Save50 = Nz(![Collator Delivery])
  184. Save51 = Nz(![Offline Machine No])
  185. Save52 = Nz(![Calc roto Hrs])
  186. Save53 = Nz(![Feet Per Core roto])
  187. Save54 = Nz(![Order Complete])
  188. Save55 = Nz(![Collator Speed])
  189. Save56 = Nz(![Offline Speed])
  190. Save57 = Nz(![No Wide Thru Collator])
  191. Save58 = Nz(![Calc Length Thru Collator])
  192. Save59 = Nz(![No Wide Thru Roto])
  193. Save60 = Nz(![Calc Length Thru roto])
  194. Save61 = Nz(![Completed roto Hrs])
  195. Save62 = Nz(![Remaining roto Hrs])
  196. Save63 = Nz(![Offline Delivery roto])
  197. Save64 = Nz(![Exact Repeat])
  198. Save65 = Nz(![Cores Due])
  199. Save66 = Nz(![Cartons Due])
  200. Save67 = Nz(![Ribbons Due])
  201. Save68 = Nz(![Laminate Due])
  202. Save69 = Nz(![Cleaning Cards Due])
  203. Save70 = Nz(![Chip Due])
  204. Save71 = Nz(![Tamarack])
  205. Save72 = Nz(![Backer Die Due])
  206. Save73 = Nz(![Quadrel])
  207. Save74 = Nz(![Quadrel No])
  208. Save75 = Nz(![Quadrel Date])
  209. Save76 = Nz(![completed quad hrs])
  210. Save77 = Nz(![Remaining Hrs- Quadrel])
  211. Save78 = Nz(![Calc Length Thru quardel])
  212. Save79 = Nz(![Tamarack Machine No])
  213. Save80 = Nz(![Tamarack date])
  214. Save81 = Nz(![Remaining Hr-Tamarack])
  215. Save82 = Nz(![Calc Length Thru tamarack])
  216. Save83 = Nz(![completed tam hrs])
  217. Save84 = Nz(![Roto Machine No])
  218. Save85 = Nz(![Quad speed])
  219. Save86 = Nz(![tam speed])
  220. Save87 = Nz(![roto speed])
  221. Save88 = Nz(![Feet Per Core tam])
  222. Save89 = Nz(![Feet Per Core quad])
  223. Save90 = Nz(![Offline Delivery tam])
  224. Save91 = Nz(![Offline Delivery quad])
  225. Save92 = Nz(![No Wide Thru quad])
  226. Save93 = Nz(![No Wide Thru Tam])
  227. Save94 = Nz(![Special Pallets Due])
  228. Save95 = Nz(![Security Tape Due])
  229. Save96 = Nz(![Content Number])
  230. Save97 = Nz(![Plate Length])
  231. Save98 = Nz(![Number Wide Plates])
  232.         .Close
  233.         Set ArchiveSet = Nothing
  234.     End With
  235.  
  236.     StrSQL = "SELECT [Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
  237.     "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
  238.     "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
  239.     "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
  240.     "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
  241.     "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
  242.     "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
  243.     "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
  244.     "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
  245.     "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
  246.     "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due]," & _
  247.     "[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
  248.     "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs]," & _
  249.     "[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
  250.     "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed]," & _
  251.     "[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
  252.     "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number]," & _
  253.     "[Order Master].[Plate Length],[Order Master].[Number Wide Plates] FROM [Order Master];"
  254.     Set CurrentSet = MyDb.OpenRecordset(StrSQL)
  255.  
  256.     With CurrentSet
  257.         .AddNew
  258.         ![Order Number] = Save2
  259.  
  260.         ![Customer] = Save4
  261. ![Received] = Save5
  262. ![Need By] = Save6
  263. ![Sales Rep] = Save7
  264. ![Parts] = Save8
  265. ![Scheduled Ship] = Save9
  266. ![Width] = Save10
  267. ![Length] = Save11
  268. ![Quantity] = Save12
  269. ![Delivery Method] = Save13
  270. ![Midax] = Save14
  271. ![WCSS] = Save15
  272. ![Job Type] = Save16
  273. ![Why] = Save17
  274. ![Service Rep] = Save18
  275. ![Why Reop] = Save19
  276. ![Plate Date] = Save20
  277. ![Press Date] = Save21
  278. ![Press Sequence] = Save22
  279. ![Collator Date] = Save23
  280. ![Offline Date] = Save24
  281. ![Paper Due] = Save25
  282. ![Carbon Due] = Save26
  283. ![Die Due] = Save27
  284. ![Ink Due] = Save28
  285. ![Repeat Order Numbers] = Save29
  286. ![Misc Due] = Save30
  287. ![Companion Order] = Save31
  288. ![Companion Order Number] = Save32
  289. ![Press] = Save33
  290. ![No Parts] = Save34
  291. ![No Wide] = Save35
  292. ![Calc Length] = Save36
  293. ![Speed] = Save37
  294. ![Calc Hrs] = Save38
  295. ![Completed] = Save39
  296. ![Remaining] = Save40
  297. ![Additional Hrs] = Save41
  298. ![No of Inks Face] = Save42
  299. ![No of Inks Back] = Save43
  300. ![Die Number(s) Face] = Save44
  301. ![Die Number(s) Liner] = Save45
  302. ![Collator No:] = Save46
  303. ![Calc Collator Hours] = Save47
  304. ![CompletedC] = Save48
  305. ![RemainingC] = Save49
  306. ![Collator Delivery] = Save50
  307. ![Offline Machine No] = Save51
  308. ![Calc roto Hrs] = Save52
  309. ![Feet Per Core roto] = Save53
  310. ![Order Complete] = Save54
  311. ![Collator Speed] = Save55
  312. ![Offline Speed] = Save56
  313. ![No Wide Thru Collator] = Save57
  314. ![Calc Length Thru Collator] = Save58
  315. ![No Wide Thru Roto] = Save59
  316. ![Calc Length Thru roto] = Save60
  317. ![Completed roto Hrs] = Save61
  318. ![Remaining roto Hrs] = Save62
  319. ![Offline Delivery roto] = Save63
  320. ![Exact Repeat] = Save64
  321. ![Cores Due] = Save65
  322. ![Cartons Due] = Save66
  323. ![Ribbons Due] = Save67
  324. ![Laminate Due] = Save68
  325. ![Cleaning Cards Due] = Save69
  326. ![Chip Due] = Save70
  327. ![Tamarack] = Save71
  328. ![Backer Die Due] = Save72
  329. ![Quadrel] = Save73
  330. ![Quadrel No] = Save74
  331. ![Quadrel Date] = Save75
  332. ![completed quad hrs] = Save76
  333. ![Remaining Hrs- Quadrel] = Save77
  334. ![Calc Length Thru quardel] = Save78
  335. ![Tamarack Machine No] = Save79
  336. ![Tamarack date] = Save80
  337. ![Remaining Hr-Tamarack] = Save81
  338. ![Calc Length Thru tamarack] = Save82
  339. ![completed tam hrs] = Save83
  340. ![Roto Machine No] = Save84
  341. ![Quad speed] = Save85
  342. ![tam speed] = Save86
  343. ![roto speed] = Save87
  344. ![Feet Per Core tam] = Save88
  345. ![Feet Per Core quad] = Save89
  346. ![Offline Delivery tam] = Save90
  347. ![Offline Delivery quad] = Save91
  348. ![No Wide Thru quad] = Save92
  349. ![No Wide Thru Tam] = Save93
  350. ![Special Pallets Due] = Save94
  351. ![Security Tape Due] = Save95
  352. ![Content Number] = Save96
  353. ![Plate Length] = Save97
  354. ![Number Wide Plates] = Save98
  355.         .Update
  356.         .Close
  357.         Set CurrentSet = Nothing
  358.     End With
  359.     MsgBox "record added with order number " + Combo970.Value
  360.  
  361.     Combo960.Text = Combo970.Value
  362.  
  363.  
  364.     ' So Now we have a new record in the table, its a matter of getting to it on the form
  365.  
  366.    'Me.Requery
  367.     '  DoCmd.GoToRecord acLast
  368. End Sub
Feb 6 '18 #35
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

NOTE: ive tried
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboBox1_Click()
  2. ComboBox2.Value = ComboBox1.Text
  3. End Sub
Attached Images
File Type: jpg combobox-vbforum.jpg (23.4 KB, 77 views)
Feb 6 '18 #36

Sign in to post your reply or Sign up for a free account.

Similar topics

0
use Excel2000 as web interface to collect user input?
by: Michael | last post by:
Hi All, I am working on my intranet reporting web site, by use of IIS5 + Office2000 +SQL2000. Currently, the first step, I show user an ASP web-interface to collect user input, keyword...
ASP / Active Server Pages
2
how ASP.NET page gets user input from another ASP.NET page??
by: Matthew Louden | last post by:
In ASP, if I have asppage.asp for GUI and aspprocess.asp for process requests from database. In asppage.asp code will be like: <form action="aspprocess.asp" method="POST"> GUI CODE </form> ...
ASP.NET
4
Capture whole child form in VB.Net
by: jxiang | last post by:
I created a child form that is much bigger than the MDI form in VB.Net. I am trying to capture the whole child form and save as an image or sent to printer. I tried to use BitBlt to capture the...
Visual Basic .NET
2
Putting a user input in a textarea onload
by: danielboendergaard | last post by:
Hey Im making a homepage in php. I use a html form to put data into mysql and i want to make some buttons which inserts user input values into a textarea. I have used a button like this: <input...
Javascript
2
How to capture user clicking form's "X" (within the controlbox)
by: =?Utf-8?B?TG9zdEluTUQ=?= | last post by:
Hi All, I am trying to determine if the user clicked the "x" close button within the controlbox of the form (top right corner - next to minimize and maximize buttons). I am using the...
Visual Basic .NET
29
parshupooja
Form which accepts & validate user input and saves that input to SQL Server
by: parshupooja | last post by:
Hi, I am working in Asp.Net 2.0, SQL Server, C#. I am trying to create a User Input form which has lots of fields such as : salutation; Firstname: lastname: address: Zip: Phone:
.NET Framework
0
capturing user input
by: Cigar2011 | last post by:
I have a MYSQL database set up. I also have a html form created. The form requests' the users first and last names, address (with different fields for town/state/zip), email and phone. I want to...
MySQL Database
2
Display input form results in another frame
by: Abneo | last post by:
Hi all, I am very new to Javascript and I am in some need of some help. I am creating a site that has a madlib. I got the madlib code but now I want the results of the madlib to display in...
Javascript
2
wxPython: How to Save/Store/Capture/Return user input text.
by: ekasII | last post by:
I'm trying to capture text input by user to file, password = wx.TextCtrl(self.panel_1,wx.ID_ANY,pos=(230,150),size=(200,20),style= wx.TE_PASSWORD) l__password_file = <path\file.txt>...
Python
0
Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
General
0
Basic Javascript concepts
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
Javascript
0
Batch import of multiple excel files into the database
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
Data Management
0
Merging data from multiple Excel files
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
0
BarryA
Navigating the Data Structures and Algorithms (DSA)
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
Algorithms / Advanced Math
1
Looking to do Android software development, any suggestions? Is flutter better?
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
General
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!