Code is adding an extra record every time

[mooreb55020]

When I add a new record from a form the code is adding 2 records 1 with all the actual data and 1 with just part of the data.

Private Sub Command22_Click()
Dim tProducts As DAO.Recordset
Set tProducts = CurrentDb.OpenRecordset("SELECT * FROM tProducts")
If Me.tProductLine.Value = 1 Then
Dim SQL1 As Integer
Dim Code1 As Integer
Dim NewID1 As String
SQL1 = DMax("ProductCode", "tProducts", "ProductLine=1")
Code1 = SQL1 + 1
tProducts.AddNew
tProducts![ProductCode] = Code1
NewID1 = "01" & Format(Code1, "00000")
tProducts![ItemCode] = NewID1
Else
If Me.tProductLine.Value = 2 Then
Dim SQL2 As Integer
Dim Code2 As Integer
Dim NewID2 As String
SQL2 = DMax("ProductCode", "tProducts", "ProductLine=2")
Code2 = SQL2 + 1
tProducts.AddNew
tProducts![ProductCode] = Code2
NewID2 = "02" & Format(Code2, "00000")
tProducts![ItemCode] = NewID2
Else
If Me.tProductLine.Value = 3 Then
Dim SQL3 As Integer
Dim Code3 As Integer
Dim NewID3 As String
SQL3 = DMax("ProductCode", "tProducts", "ProductLine=3")
Code3 = SQL3 + 1
tProducts.AddNew
tProducts![ProductCode] = Code3
NewID3 = "03" & Format(Code3, "00000")
tProducts![ItemCode] = NewID3
Else
If Me.tProductLine.Value = 4 Then
Dim SQL4 As Integer
Dim Code4 As Integer
Dim NewID4 As String
SQL4 = DMax("ProductCode", "tProducts", "ProductLine=4")
Code4 = SQL4 + 1
tProducts.AddNew
tProducts![ProductCode] = Code4
NewID4 = "04" & Format(Code4, "00000")
tProducts![ItemCode] = NewID4
Else
If Me.tProductLine.Value = 5 Then
Dim SQL5 As Integer
Dim Code5 As Integer
Dim NewID5 As String
SQL5 = DMax("ProductCode", "tProducts", "ProductLine=5")
Code5 = SQL5 + 1
tProducts.AddNew
tProducts![ProductCode] = Code5
NewID5 = "05" & Format(Code5, "00000")
tProducts![ItemCode] = NewID5
Else
If Me.tProductLine.Value = 6 Then
Dim SQL6 As Integer
Dim Code6 As Integer
Dim NewID6 As String
SQL6 = DMax("ProductCode", "tProducts", "ProductLine=6")
Code6 = SQL6 + 1
tProducts.AddNew
tProducts![ProductCode] = Code6
NewID6 = "06" & Format(Code6, "00000")
tProducts![ItemCode] = NewID6
Else
If Me.tProductLine.Value = 7 Then
Dim SQL7 As Integer
Dim Code7 As Integer
Dim NewID7 As String
SQL7 = DMax("ProductCode", "tProducts", "ProductLine=7")
Code7 = SQL7 + 1
tProducts.AddNew
tProducts![ProductCode] = Code7
NewID7 = "07" & Format(Code7, "00000")
tProducts![ItemCode] = NewID7
Else
If Me.tProductLine.Value = 8 Then
Dim SQL8 As Integer
Dim Code8 As Integer
Dim NewID8 As String
SQL8 = DMax("ProductCode", "tProducts", "ProductLine=8")
Code8 = SQL8 + 1
tProducts.AddNew
tProducts![ProductCode] = Code8
NewID8 = "30" & Format(Code8, "00000")
tProducts![ItemCode] = NewID8
Else
If Me.tProductLine.Value = 9 Then
Dim SQL9 As Integer
Dim Code9 As Integer
Dim NewID9 As String
SQL9 = DMax("ProductCode", "tProducts", "ProductLine=9")
Code9 = SQL9 + 1
tProducts.AddNew
tProducts![ProductCode] = Code9
NewID9 = "40" & Format(Code9, "00000")
tProducts![ItemCode] = NewID9
Else
If Me.tProductLine.Value = 10 Then
Dim SQL10 As Integer
Dim Code10 As Integer
Dim NewID10 As String
SQL10 = DMax("ProductCode", "tProducts", "ProductLine=10")
Code10 = SQL10 + 1
tProducts.AddNew
tProducts![ProductCode] = Code10
NewID10 = "50" & Format(Code10, "00000")
tProducts![ItemCode] = NewID10
Else
If Me.tProductLine.Value = 11 Then
Dim SQL11 As Integer
Dim Code11 As Integer
Dim NewID11 As String
SQL11 = DMax("ProductCode", "tProducts", "ProductLine=11")
Code11 = SQL11 + 1
tProducts.AddNew
tProducts![ProductCode] = Code11
NewID11 = "60" & Format(Code11, "00000")
tProducts![ItemCode] = NewID11
Else
If Me.tProductLine.Value = 12 Then
Dim SQL12 As Integer
Dim Code12 As Integer
Dim NewID12 As String
SQL12 = DMax("ProductCode", "tProducts", "ProductLine=12")
Code12 = SQL12 + 1
tProducts.AddNew
tProducts![ProductCode] = Code12
NewID12 = "70" & Format(Code12, "00000")
tProducts![ItemCode] = NewID12
Else
If Me.tProductLine.Value = 13 Then
Dim SQL13 As Integer
Dim Code13 As Integer
Dim NewID13 As String
SQL13 = DMax("ProductCode", "tProducts", "ProductLine=13")
Code13 = SQL13 + 1
tProducts.AddNew
tProducts![ProductCode] = Code13
NewID13 = "90" & Format(Code13, "00000")
tProducts![ItemCode] = NewID13
Else
MsgBox "You Must enter a Product Line in order to submit a new product!", vbOKOnly, "#ERROR!"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


tProducts![ItemDescription] = Me.tItemDescription.Value
tProducts![UoM] = Me.tUoM.Value
tProducts![ProductLine] = Me.tProductLine.Value
tProducts![Aquisition] = Me.tAquasition.Value
tProducts![Active] = Me.ynActive.Value
tProducts![BOMRequired] = Me.ynBOM.Value
tProducts![AssemblyRequired] = Me.ynAss.Value
tProducts![Component] = Me.ynComponent.Value
tProducts![CoARequired] = Me.ynCoA.Value
tProducts.Update
DoCmd.Requery
tProducts.Close
DoCmd.Close


End Sub

the only thing I can imagine is that i have the form property DataEntry set to "Yes" so that when it pops up it shows up as a blank form. Could this be whats causing the duplicate entry? if so how do i fix that. If the code is the problem, please help me fix it.

Thanks in advance!

[ranman256]

you should be using QUERIES to add data , not code.
It may be part of the problem.

[ranman256]

use case statments. (but dont, use queries)

Private Sub Command22_Click()
Dim tProducts As DAO.Recordset
Dim SQL1 As Integer
Dim Code1 As Integer
Dim NewID1 As String


Set tProducts = CurrentDb.OpenRecordset("SELECT * FROM tProducts")
Select Case Me.tProductLine.Value
Case 1


    SQL1 = DMax("ProductCode", "tProducts", "ProductLine=1")
    Code1 = SQL1 + 1
    tProducts.AddNew
    tProducts![ProductCode] = Code1
    NewID1 = "01" & Format(Code1, "00000")
    tProducts![ItemCode] = NewID1
Case 2


    SQL1 = DMax("ProductCode", "tProducts", "ProductLine=2")
    Code1 = SQL1 + 1
    tProducts.AddNew
    tProducts![ProductCode] = Code2
    NewID1 = "02" & Format(Code1, "00000")
    tProducts![ItemCode] = NewID1
Case 3


'etc , etc
End Select




tProducts![ItemDescription] = Me.tItemDescription.Value
tProducts![UoM] = Me.tUoM.Value
tProducts![ProductLine] = Me.tProductLine.Value
tProducts![Aquisition] = Me.tAquasition.Value
tProducts![Active] = Me.ynActive.Value
tProducts![BOMRequired] = Me.ynBOM.Value
tProducts![AssemblyRequired] = Me.ynAss.Value
tProducts![Component] = Me.ynComponent.Value
tProducts![CoARequired] = Me.ynCoA.Value
tProducts.Update
DoCmd.Requery
tProducts.Close
DoCmd.Close


End Sub

[davegri]

tProducts![ItemDescription] = Me.tItemDescription.Value
tProducts![UoM] = Me.tUoM.Value
tProducts![ProductLine] = Me.tProductLine.Value
tProducts![Aquisition] = Me.tAquasition.Value
tProducts![Active] = Me.ynActive.Value
tProducts![BOMRequired] = Me.ynBOM.Value
tProducts![AssemblyRequired] = Me.ynAss.Value
tProducts![Component] = Me.ynComponent.Value
tProducts![CoARequired] = Me.ynCoA.Value
tProducts.Update
DoCmd.Requery
tProducts.Close
DoCmd.Close

Are these Me. references on the right side bound to the table?
Data Entry = YES means that the form is opened blank, but in data entry mode ready for data to be typed in for a new record.
So any data typed into the form will create a new record, regardless of the VBA code also creating a record.

[mooreb55020]

Davegri,

the Me. references are the textboxes in the form. I'm trying to learn access because the company I work originally had me working with excel that I thought was for maybe a couple instances which turned into basically building an entire ERP/MRP system in excel. SMH! The more I developed the more they asked for and I finally got to the point that I needed to learn a database system and the company was only willing to buy access for this purpose. I have more experience in SQL Server but I'm only like 60% proficient in that.

[davegri]

the Me. references are the textboxes in the form

OK, but the pertinent question is: Are they bound? If so, that's the problem, or part of the problem.
What is the recordsource for the form?
Did you have the Access Wizard help create the form?
Can you post the DB here for us to take a look at?

[mooreb55020]

Form recordsource is tProducts, recordset type is dynaset.
I built the form by myself I don't feel comfortable building it through wizard.
for some reason I can't upload the file. I'm pretty new to this forum and not sure how to do that other than through attachments.

[orange]

Posting a copy of your database:
Do a compact and repair on a copy of your database. Then, create a zip file. Use Go Advanced button, then Manage attachments.

If there are specifics to illustrate a problem, then please provide details to help readers get to the problem area.

[mooreb55020]

Database.zip

Thanks for all your help!

[davegri]

OK, here you go. Binding the form to the table was the problem. I removed all that.
You're using the ID of the ProductLine table to determine the product line instead of the 'code' in that table. Shouldn't use ID like that, use the code instead.
Codes are 1,2,3,4,5,6,7,30,40,50,60,70,90. Adjusted VBA for this sequence.
Changed the combo box tProductLine.rowsource to include the product codes above, and got the product code in the vba from the combo box; the column(2).

mooreb-davegri-v01.zip

[mooreb55020]

works perfectly. I'm just looking through all the changes because there are 4 more tables that i need to make very similarly. This project is way above my head but no one else at work is willing to learn to do it. hahaha

[davegri]

You can replace ALL the VBA behind fNewProduct with below.
VBA will automatically handle any new productlines added in the future. No need to hard-wire the ProductLines in the VBA.
It includes VBA to check for required entries in the form.
VBA tested OK.

Your table currently contains many duplicate ProductCodes, which doesn't seem right.
If those are meant to uniquely identify the products, the ID would be better to use. That's its inherent function anyway.

Additional note:
If you intend to use this form for adding more than one product at a time, the controls will need to be blanked out
after the tProducts.Update, otherwise the entries will remain on the form. It's not a problem now, as the form
closes itself after adding the record.

Option Compare Database
Option Explicit
Private Sub Command22_Click()
    Dim tProducts As DAO.Recordset
    Dim nProductLine As Integer
    Dim nCodePlus As Long
    'first verify that all required fields are present
    If IsNull(Me.tProductLine) Then
        MsgBox "No ProductLine selected, required", vbOKOnly, "R E Q U I R E D"
        Exit Sub
    End If
    If IsNull(Me.tItemDescription) Then
        MsgBox "Item Description is required.", vbOKOnly, "R E Q U I R E D"
        Exit Sub
    End If
    If IsNull(Me.tAquasition) Then
        MsgBox "Acquisition entry is required", vbOKOnly, "R E Q U I R E D"
        Exit Sub
    End If
    If IsNull(Me.tUoM) Then
        MsgBox "Unit of Measure entry is required", vbOKOnly, "R E Q U I R E D"
        Exit Sub
    End If
    
    Set tProducts = CurrentDb.OpenRecordset("SELECT * FROM tProducts")
    'get the productline code from the combobox
    nProductLine = Me.tProductLine.Column(2)
    'find highest productcode in the productline and add 1 to it
    nCodePlus = Nz(DMax("ProductCode", "tProducts", "ProductLine=" & nProductLine), 0) + 1
    'Add the new product
    tProducts.AddNew
    tProducts!ProductCode = nCodePlus
    tProducts!ItemCode = Format(nProductLine, "00") & Format(nCodePlus, "00000")
    
    tProducts![ItemDescription] = Me.tItemDescription
    tProducts![UoM] = Me.tUoM
    tProducts![ProductLine] = Me.tProductLine
    tProducts![Aquisition] = Me.tAquasition
    tProducts![Active] = Me.ynActive
    tProducts![BOMRequired] = Me.ynBOM
    tProducts![AssemblyRequired] = Me.ynAss
    tProducts![Component] = Me.ynComponent
    tProducts![CoARequired] = Me.ynCoA
    tProducts.Update
    Set tProducts = Nothing
    DoCmd.Close acForm, Me.Name
End Sub

[mooreb55020]

perfect it works

[davegri]

it's not pulling the max value from the product code field instead it's just putting everything as 1

The original code was posted today at 8:45, but I made some revisions to that post and changed the shown code at 11:37. Are you using the latest code?

[mooreb55020]

It's working Perfectly now with the revision you placed! Thanks again