Need help to copy the next available ID in ms acces form combo field

I have a button in a form which creates a new record in a form and copies values from the record in a new record. This works fine but I need the change in a shipID combo field. It copies now the same value but I need the next available ID.

If the copied form has the value Brussel it needs the next available value

ShipID      ShipCity
15      Amsterdam
16      Brussel
56      London


Private Sub copy_fields_Click()

Dim v22 As Variant
Dim v23 As Variant
Dim v24 As Variant
Dim v25 As Variant
Dim v26 As Variant
Dim v27 As Variant
Dim v28 As Variant
Dim v29 As Variant
Dim v30 As Variant
Dim v31 As Variant
Dim v32 As Variant
Dim v33 As Variant


v22 = Me!shipID.value
v23 = Forms![Workorders]![Workorder Parts].Form![Combo14].value
v24 = Forms![Workorders]![Workorder Parts].Form![Quantity].value
v25 = Forms![Workorders]![Workorder Parts].Form![ArtNrKlant].value
v26 = Forms![Workorders]![Workorder Parts].Form![HE].value
v27 = Forms![Workorders]![Workorder Parts].Form![UnitPrice].value
v28 = Forms![Workorders]![Workorder Parts].Form![BTW].value
v29 = Forms![Workorders]![SubPallets].Form![PalletID].value
v30 = Forms![Workorders]![SubPallets].Form![TransactionDate].value
v31 = Forms![Workorders]![SubPallets].Form![DeliveredBy].value
v32 = Forms![Workorders]![SubPallets].Form![DeliveredTo].value
v33 = Forms![Workorders]![SubPallets].Form![Delivered].value

RunCommand acCmdRecordsGoToNew


Me!shipID = v22
Forms![Workorders]![Workorder Parts].Form![Combo14] = v23
Forms![Workorders]![Workorder Parts].Form![Quantity] = v24
Forms![Workorders]![Workorder Parts].Form![ArtNrKlant] = 25
Forms![Workorders]![Workorder Parts].Form![HE] = v26
Forms![Workorders]![Workorder Parts].Form![UnitPrice] = v27
Forms![Workorders]![Workorder Parts].Form![BTW] = v28
Forms![Workorders]![SubPallets].Form![PalletID] = v29
Forms![Workorders]![SubPallets].Form![TransactionDate] = v30
Forms![Workorders]![SubPallets].Form![DeliveredBy] = v31
Forms![Workorders]![SubPallets].Form![DeliveredTo] = v32
Forms![Workorders]![SubPallets].Form![Delivered] = v33

End Sub

Open in new window

mvdwalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what is the row source of the combo box "shipID" ?
0
Rey Obrero (Capricorn1)Commented:
change line 32  Me!shipID = v22

with

if shipID is Number data type,
              Me!shipID = dmin("shipID","tablex","shipID > " & v22)

if shipID is Text data type,
            Me!shipID = dmin("shipID","tablex","shipID > '" & v22 & "'")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mvdwalAuthor Commented:
SELECT DISTINCTROW ScheduleID.ShipID, ScheduleID.ShipCity, ScheduleID.ShipZIPCode, ScheduleID.ShipAddress, ScheduleID.CustomerID FROM ScheduleID WHERE (((ScheduleID.CustomerID)=[forms]![Workorders by Customer].[Workorders by Customer Subform].[form]![CustomerID]));

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mvdwalAuthor Commented:
if shipID is Number data type,
               Me!shipID = dmin("shipID","ScheduleID","shipID > " & v22)

 if shipID is Text data type,
             Me!shipID = dmin("shipID","ScheduleID","shipID > '" & v22 & "'")
0
Rey Obrero (Capricorn1)Commented:
what  data type are CustomerID and shipID? Number or Text?
0
mvdwalAuthor Commented:
Works Great! Thank you very much!
0
mvdwalAuthor Commented:
Number data type

Me!shipID = dmin("shipID","ScheduleID","shipID > " & v22)

Open in new window

0
Rey Obrero (Capricorn1)Commented:
you may have to change the code with this

Me!shipID = dmin("shipID","ScheduleID","shipID > " & v22 & " And CustomerID= " & [forms]![Workorders by Customer].[Workorders by Customer Subform].[form]![CustomerID])
0
mvdwalAuthor Commented:
Dear Rey,

If I push the button It copies also the fields from the subform. This is great except it copies only the first row. Is there a way to fix this?  

Private Sub copy_fields_Click()
Dim v0 As Variant
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
Dim v5 As Variant
Dim v6 As Variant
Dim v7 As Variant
Dim v8 As Variant
Dim v9 As Variant
Dim v10 As Variant
Dim v11 As Variant
Dim v12 As Variant
Dim v13 As Variant
Dim v14 As Variant
Dim v15 As Variant
Dim v16 As Variant
Dim v17 As Variant
Dim v18 As Variant
Dim v19 As Variant
Dim v20 As Variant
Dim v21 As Variant
Dim v22 As Variant
Dim v23 As Variant
Dim v24 As Variant
Dim v25 As Variant
Dim v26 As Variant
Dim v27 As Variant
Dim v28 As Variant
Dim v29 As Variant
Dim v30 As Variant
Dim v31 As Variant
Dim v32 As Variant
Dim v33 As Variant
Dim v34 As Variant
Dim v35 As Variant


v0 = Me!Customer_EmployeeID.value
v1 = Me!DateReceived.value
v2 = Me!DateRequired.value
v3 = Me!MakeAndModel.value
v4 = Me!ShippingID.value
v5 = Me!EmployeeID.value
v6 = Me!DeliveryNote.value
v7 = Me!DeliveryNotes.value
v8 = Me!PurchaseOrderNumber.value
v9 = Me!Warehouse.value
v10 = Me!Barcode.value
v11 = Me!WGR.value
v12 = Me!Extra.value
v13 = Me!ProblemDescription.value
v14 = Me!SerialNumber.value
v15 = Me!DateFinished.value
v16 = Me!DatePickedUp.value
v17 = Me!SalesTaxRate.value
v18 = Me!DateReceived.value
v19 = Me!DateRequired.value
v20 = Me!DelTimeEarly.value
v21 = Me!Tekst98.value
v22 = Me!Combo58.value
v23 = Forms![Workorders]![Workorder Parts].Form![Combo14].value
v24 = Forms![Workorders]![Workorder Parts].Form![Quantity].value
v25 = Forms![Workorders]![Workorder Parts].Form![ArtNrKlant].value
v26 = Forms![Workorders]![Workorder Parts].Form![HE].value
v27 = Forms![Workorders]![Workorder Parts].Form![UnitPrice].value
v28 = Forms![Workorders]![Workorder Parts].Form![BTW].value
v29 = Forms![Workorders]![SubPallets].Form![PalletID].value
v30 = Forms![Workorders]![SubPallets].Form![TransactionDate].value
v31 = Forms![Workorders]![SubPallets].Form![DeliveredBy].value
v32 = Forms![Workorders]![SubPallets].Form![DeliveredTo].value
v33 = Forms![Workorders]![SubPallets].Form![Delivered].value
v34 = Forms![Workorders]![Workorder Parts].Form![ICHE].value
v35 = Forms![Workorders]![Workorder Parts].Form![OCVE].value
RunCommand acCmdRecordsGoToNew

Me!Customer_EmployeeID = v0
Me!DateReceived = v1
Me!DateRequired = v2
Me!MakeAndModel = v3
Me!ShippingID = v4
Me!EmployeeID = v5
Me!DeliveryNote = v6
Me!DeliveryNotes = v7
Me!PurchaseOrderNumber = v8
Me!Warehouse = v9
Me!Barcode = v10
Me!WGR = v11
Me!Extra = v12
Me!ProblemDescription = v3
Me!SerialNumber = v14
Me!DateFinished = v15
Me!DatePickedUp = v16
Me!SalesTaxRate = v17
Me!DateReceived = v18
Me!DateRequired = v19
Me!DelTimeEarly = v20
Me!Tekst98 = v21
'Me!Combo58 = v22
Forms![Workorders]![Workorder Parts].Form![Combo14] = v23
Forms![Workorders]![Workorder Parts].Form![Quantity] = v24
Forms![Workorders]![Workorder Parts].Form![ArtNrKlant] = v25
Forms![Workorders]![Workorder Parts].Form![HE] = v26
Forms![Workorders]![Workorder Parts].Form![UnitPrice] = v27
Forms![Workorders]![Workorder Parts].Form![BTW] = v28
Forms![Workorders]![SubPallets].Form![PalletID] = v29
Forms![Workorders]![SubPallets].Form![TransactionDate] = v30
Forms![Workorders]![SubPallets].Form![DeliveredBy] = v31
Forms![Workorders]![SubPallets].Form![DeliveredTo] = v32
Forms![Workorders]![SubPallets].Form![Delivered] = v33
Forms![Workorders]![Workorder Parts].Form![ICHE] = v34
Forms![Workorders]![Workorder Parts].Form![OCVE] = v35

Me!Combo58 = DMin("shipID", "ScheduleID", "shipID > " & v22)

Me.Repaint



End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.