Make two records that are Similar (1 Viewer)

K

kjbrack

Registered User.
Local time
Today, 06:43
Joined
Mar 3, 2015
Messages
20
I’m new to this so please be kind.

I am creating a simple inventory db. I have a form that we enter into the table inventory. Right now we enter in everything once as inventory, then again to out to coating or ship to customer. I’m looking for the code to create a button enter the record twice but make two fields change.

So in short terms I want the form to enter a record with all the data as displayed. Then create another record with all the same data but the quantity would need to change to a negative and the Status would change to “Coating”.


I know this code won't work but this is what I want to do.


Private Sub Command78_Click()

.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
!Twist = Me!Combo33
!Style = Me!Combo35
!GasSystem = Me!Combo44
!GasPort = Me!Combo37
!Marking = Me!Combo58
!Finish = Me!Combo60
!Quantity = Me!Text64
!Status = Me!Combo62 'Default value "Inventory"

.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
!Twist = Me!Combo33
!Style = Me!Combo35
!GasSystem = Me!Combo44
!GasPort = Me!Combo37
!Marking = Me!Combo58
!Finish = Me!Combo60
!Quantity = Me!Text64 * -1
!Status = Me!Combo79 'Default value "Coating"

DoCmd.Close

End Sub
 
Last edited:
S

stumac

Registered User.
Local time
Today, 03:43
Joined
Feb 1, 2013
Messages
17
If you are using an unbound form then the code you have isn't far away.
Code: 
Private Sub Command78_Click()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Yourtable", dbOpenDynaset)

With rs
.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
!Twist = Me!Combo33
!Style = Me!Combo35
!GasSystem = Me!Combo44
!GasPort = Me!Combo37
!Marking = Me!Combo58
!Finish = Me!Combo60
!Quantity = Me!Text64
!Status = "Inventory"
.update

.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
!Twist = Me!Combo33
!Style = Me!Combo35
!GasSystem = Me!Combo44
!GasPort = Me!Combo37
!Marking = Me!Combo58
!Finish = Me!Combo60
!Quantity = Me!Text64 * -1
!Status = "Coating"
.Update

.Close
End With

Set rs = Nothing
Docmd.close
End sub
 
Last edited:
K

kjbrack

Registered User.
Local time
Today, 06:43
Joined
Mar 3, 2015
Messages
20
Worked exactly right.

Thanks, problem solved
 
S

stumac

Registered User.
Local time
Today, 03:43
Joined
Feb 1, 2013
Messages
17
No problem, one thing to watch - if the quantity can be a null value you might want to use the Nz function or you will get an invalid use of null error.

Code: 
!Quantity = Nz(Me!Text64) * -1
 
K

kjbrack

Registered User.
Local time
Today, 06:43
Joined
Mar 3, 2015
Messages
20
How to make it where they have to have data in the field. I added this to the top of the code.

Code: 
If IsNull(Me.Text64) Then
MsgBox "Quantity cannot be left Blank"
Cancel = True
Me.Text64.SetFocus
End If
It gives me the message block, then I hit ok it closes the form and saves the record with a blank in that field. The form is unbound so before update doesn't work.

I want it to not save the record and return to the form on the blank field so they can put in a number.
 
Last edited:
S

stumac

Registered User.
Local time
Today, 03:43
Joined
Feb 1, 2013
Messages
17
If you check if its blank before you start adding the record you can just exit the code:

Code: 
Private Sub Command78_Click()

if isnull(Me.quantity) then
Me.quantity.SetFocus
MsgBox "Quantity Cannot be blank"
Exit Sub
End if

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Yourtable", dbOpenDynaset)

With rs
.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
!Twist = Me!Combo33
!Style = Me!Combo35
!GasSystem = Me!Combo44
!GasPort = Me!Combo37
!Marking = Me!Combo58
!Finish = Me!Combo60
!Quantity = Me!Text64
!Status = "Inventory"
.update

.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
!Twist = Me!Combo33
!Style = Me!Combo35
!GasSystem = Me!Combo44
!GasPort = Me!Combo37
!Marking = Me!Combo58
!Finish = Me!Combo60
!Quantity = Me!Text64 * -1
!Status = "Coating"
.Update

.Close
End With

Set rs = Nothing
Docmd.close
End sub
 
Mile-O

Mile-O

Back once again...
Local time
Today, 11:43
Joined
Dec 10, 2002
Messages
11,316
Psst! Learn to give your control's descriptive names, preferable with a prefix. Such as cboMarking, cboStyle, txtStatus, and so on.

If you come back to this database in future, you'll read your code and wonder where the hell - or even what! - Combo68 is all about.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Sep 12, 2006
Messages
15,634
what is hard to understand is the underlying structure

you shouldn't need to add the exact same multiple details to two tables.

instead, let's say you add the item to the inventory table. All the coating needs is the ID of the record from the inventory table. That's how it's all supposed to hang together.
 
K

kjbrack

Registered User.
Local time
Today, 06:43
Joined
Mar 3, 2015
Messages
20
Thanks Stumac that worked.

gemma-the-husky. I'm not adding the same details to two tables. The records are going to one table called inventory. Then I'm running a query with sum function to get the balance of each item in stock. So as the guys pack barrels into boxes that is inventory so it goes into the table as a positive number. If the barrels go into a box and they know that it is going to the coater for nitride or phosphate then it needs to be entered into the table with all the same details but the quantity needs to be a negative and the status needs to say coating. I also have another command button at the bottom of the form that says Inventory / Ship to Customer that does the same thing. Except status says customer.

Mile-O: Agree your suggestion does make sense, will make those changes.

My background. I'm a CNC machinist by trade been doing machining for almost 25 years. Currently I'm a Mfg Engineer at a firearms company that makes barrels for the AR-15, AR-10, AK-47 platforms. We run about 800 barrels a day. This db started as a simple way to track the progress of the packing department. Then into lets try and see if it can do full inventory. Any help I get is greatly appreciated as I stated at the beginning of this thread I'm a beginner and new to this program. Each barrel is labeled by these characteristics in this order: Caliber, Style, Length, Twist, Material, Gas System, Gas Port, Marking, Finish.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Sep 12, 2006
Messages
15,634
kjbrack said:
gemma-the-husky. I'm not adding the same details to two tables. The records are going to one table called inventory. Then I'm running a query with sum function to get the balance of each item in stock. So as the guys pack barrels into boxes that is inventory so it goes into the table as a positive number. If the barrels go into a box and they know that it is going to the coater for nitride or phosphate then it needs to be entered into the table with all the same details but the quantity needs to be a negative and the status needs to say coating. I also have another command button at the bottom of the form that says Inventory / Ship to Customer that does the same thing. Except status says customer.
Click to expand...

but then I would have thought you ought to have the items in a products table with the relevant fields, so that for instance this is product no 1.

then you have a movements table with fields such as, eg

productID, date, quantity, movement type.


anyway, good luck
 
K

kjbrack

Registered User.
Local time
Today, 06:43
Joined
Mar 3, 2015
Messages
20
Here is my code:

Code: 
 Private Sub Command_Coating_Click()
 If IsNull(Me.cboEmployee) Then
Me.cboEmployee.SetFocus
MsgBox "Employee Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboMaterial) Then
Me.cboMaterial.SetFocus
MsgBox "Material Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboLength) Then
Me.cboLength.SetFocus
MsgBox "Length Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboCaliber) Then
Me.cboCaliber.SetFocus
MsgBox "Caliber Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboTwist) Then
Me.cboTwist.SetFocus
MsgBox "Twist Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboStyle) Then
Me.cboStyle.SetFocus
MsgBox "Style Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboGasSystem) Then
Me.cboGasSystem.SetFocus
MsgBox "Gas System Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboGasPort) Then
Me.cboGasPort.SetFocus
MsgBox "Gas Port Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboMarking) Then
Me.cboMarking.SetFocus
MsgBox "Marking Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cbofinish) Then
Me.cbofinish.SetFocus
MsgBox "Finish Cannot be blank"
Exit Sub
End If
 If IsNull(Me.txtQuantity) Then
Me.txtQuantity.SetFocus
MsgBox "Quantity Cannot be blank"
Exit Sub
End If
 Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Inventory", dbOpenDynaset)
 With rs
.AddNew
      !Employee = Me!cboEmployee
      !Material = Me!cboMaterial
      !Length = Me!cboLength
      !Caliber = Me!cboCaliber
      !Twist = Me!cboTwist
      !Style = Me!cboStyle
      !GasSystem = Me!cboGasSystem
      !GasPort = Me!cboGasPort
      !Marking = Me!cboMarking
      !Finish = Me!cbofinish
      !Quantity = Me!txtQuantity
      !Status = "Inventory"
      !Notes = Me!Notes
      .Update
      
.AddNew
      !Employee = Me!cboEmployee
      !Material = Me!cboMaterial
      !Length = Me!cboLength
      !Caliber = Me!cboCaliber
      !Twist = Me!cboTwist
      !Style = Me!cboStyle
      !GasSystem = Me!cboGasSystem
      !GasPort = Me!cboGasPort
      !Marking = Me!cboMarking
      !Finish = Me!cbofinish
      !Quantity = Me!txtQuantity * -1
      !Status = "Coating"
      !Notes = Me!Notes
      .Update
 .Close
End With
 Set rs = Nothing
DoCmd.Close
 End Sub
 Private Sub Command_Customer_Click()
 If IsNull(Me.cboEmployee) Then
Me.cboEmployee.SetFocus
MsgBox "Employee Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboMaterial) Then
Me.cboMaterial.SetFocus
MsgBox "Material Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboLength) Then
Me.cboLength.SetFocus
MsgBox "Length Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboCaliber) Then
Me.cboCaliber.SetFocus
MsgBox "Caliber Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboTwist) Then
Me.cboTwist.SetFocus
MsgBox "Twist Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboStyle) Then
Me.cboStyle.SetFocus
MsgBox "Style Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboGasSystem) Then
Me.cboGasSystem.SetFocus
MsgBox "Gas System Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboGasPort) Then
Me.cboGasPort.SetFocus
MsgBox "Gas Port Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboMarking) Then
Me.cboMarking.SetFocus
MsgBox "Marking Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cbofinish) Then
Me.cbofinish.SetFocus
MsgBox "Finish Cannot be blank"
Exit Sub
End If
 If IsNull(Me.txtQuantity) Then
Me.txtQuantity.SetFocus
MsgBox "Quantity Cannot be blank"
Exit Sub
End If
 Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Inventory", dbOpenDynaset)
 With rs
.AddNew
      !Employee = Me!cboEmployee
      !Material = Me!cboMaterial
      !Length = Me!cboLength
      !Caliber = Me!cboCaliber
      !Twist = Me!cboTwist
      !Style = Me!cboStyle
      !GasSystem = Me!cboGasSystem
      !GasPort = Me!cboGasPort
      !Marking = Me!cboMarking
      !Finish = Me!cbofinish
      !Quantity = Me!txtQuantity
      !Status = "Inventory"
      !Notes = Me!Notes
      .Update
      
.AddNew
      !Employee = Me!cboEmployee
      !Material = Me!cboMaterial
      !Length = Me!cboLength
      !Caliber = Me!cboCaliber
      !Twist = Me!cboTwist
      !Style = Me!cboStyle
      !GasSystem = Me!cboGasSystem
      !GasPort = Me!cboGasPort
      !Marking = Me!cboMarking
      !Finish = Me!cbofinish
      !Quantity = Me!txtQuantity * -1
      !Status = "Customer"
      !Notes = Me!Notes
      .Update
 .Close
End With
 Set rs = Nothing
DoCmd.Close
 End Sub
 Private Sub Command_Inventory_Click()
 If IsNull(Me.cboEmployee) Then
Me.cboEmployee.SetFocus
MsgBox "Employee Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboMaterial) Then
Me.cboMaterial.SetFocus
MsgBox "Material Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboLength) Then
Me.cboLength.SetFocus
MsgBox "Length Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboCaliber) Then
Me.cboCaliber.SetFocus
MsgBox "Caliber Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboTwist) Then
Me.cboTwist.SetFocus
MsgBox "Twist Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboStyle) Then
Me.cboStyle.SetFocus
MsgBox "Style Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboGasSystem) Then
Me.cboGasSystem.SetFocus
MsgBox "Gas System Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboGasPort) Then
Me.cboGasPort.SetFocus
MsgBox "Gas Port Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cboMarking) Then
Me.cboMarking.SetFocus
MsgBox "Marking Cannot be blank"
Exit Sub
End If
 If IsNull(Me.cbofinish) Then
Me.cbofinish.SetFocus
MsgBox "Finish Cannot be blank"
Exit Sub
End If
 If IsNull(Me.txtQuantity) Then
Me.txtQuantity.SetFocus
MsgBox "Quantity Cannot be blank"
Exit Sub
End If
 Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Inventory", dbOpenDynaset)
 With rs
.AddNew
      !Employee = Me!cboEmployee
      !Material = Me!cboMaterial
      !Length = Me!cboLength
      !Caliber = Me!cboCaliber
      !Twist = Me!cboTwist
      !Style = Me!cboStyle
      !GasSystem = Me!cboGasSystem
      !GasPort = Me!cboGasPort
      !Marking = Me!cboMarking
      !Finish = Me!cbofinish
      !Quantity = Me!txtQuantity
      !Status = "Inventory"
      !Notes = Me!Notes
      .Update
.Close
End With
 Set rs = Nothing
DoCmd.Close
 
End Sub

Works Great!!!:):):) Thanks for your help!!!
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:43
Joined
Jul 9, 2003
Messages
16,271
kjbrack said:
Here is my code:
Click to expand...
Due to a bug in the forum software this message was "unapproved" (hidden) for some considerable time. I have just approved it. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom