If statement no working

[bmoyer123]

In code below,

Code: Select all

If ComboQCType = BAG Then
    rsAddProducts("BAGID") = Chr(81) & Chr(67) & numQC
    Else
    rsAddProducts("BAGID") = Chr(81) & Chr(86) & numQC[/b]
End If

is not working. I want it to do the following:
If ComboQCType is equal to BAG then I want it to enter QC1 etc to "BAGID" Field. This is working, but if I ComboQCType is equal to VIAL I want it to enter QV1 etc it enters QC1 etc not QV1. What did I do wrong?

Code: Select all

Option Compare Database

Dim dbs As DAO.Database ' variable for recordset
Dim rsDivisions As DAO.Recordset
Dim rsNewProducts As DAO.Recordset
Dim rsAddProducts As DAO.Recordset
Dim strSQL As String ' variable for string expressions
Dim strWSQL As String
Sub WriteDivisions()
Set dbs = CurrentDb
Set rsAddProducts = dbs.OpenRecordset("tblInventory") ' open the table
rsAddProducts.MoveLast

For numdiv = 1 To Form_frmAddDivisions.Division
rsAddProducts.AddNew
rsAddProducts("COLL#") = Form_frmAddDivisions.ProductID
If numdiv > 26 Then
    rsAddProducts("BAGID") = Chr(numdiv - 26 + 64) & Chr(97)
    Else
    rsAddProducts("BAGID") = Chr(numdiv + 64) & "0"
End If
rsAddProducts("ProdCode") = Form_frmAddDivisions.ProdCode
rsAddProducts("VOLBAG") = Form_frmAddDivisions.Volume
rsAddProducts("BAGSFRZ") = Form_frmAddDivisions.Division
rsAddProducts("StorageUnitID") = Form_frmAddDivisions.Storage
rsAddProducts("SectionID") = Form_frmAddDivisions.SectionID
rsAddProducts("RackID") = Form_frmAddDivisions.Rack
rsAddProducts("StatusID") = Form_frmAddDivisions.Status
rsAddProducts.Update
Next numdiv
For numQC = 1 To Form_frmAddDivisions.QC
rsAddProducts.AddNew
rsAddProducts("COLL#") = Form_frmAddDivisions.ProductID
If ComboQCType = BAG Then
    rsAddProducts("BAGID") = Chr(81) & Chr(67) & numQC
    Else
    rsAddProducts("BAGID") = Chr(81) & Chr(86) & numQC
End If
rsAddProducts("ProdCode") = Form_frmAddDivisions.ProdCode
rsAddProducts("VOLBAG") = Form_frmAddDivisions.QCVolume
rsAddProducts("BAGSFRZ") = Form_frmAddDivisions.QC
rsAddProducts("QCType") = Form_frmAddDivisions.ComboQCType
rsAddProducts("StorageUnitID") = Form_frmAddDivisions.QCStorage
rsAddProducts("SectionID") = Form_frmAddDivisions.QCSectionID
rsAddProducts("RackID") = Form_frmAddDivisions.QCRack
rsAddProducts("StatusID") = Form_frmAddDivisions.Status
rsAddProducts.Update
Next numQC
End Sub

[HansV]

Is BAG a text value? If so, you should enclose it in quotes:

Code: Select all

If ComboQCType = "BAG" Then

[bmoyer123]

I satarted out with BAG in quotes, but when I do that it seems to skip the first If statement and does the Else statement.

[HansV]

Perhaps ComboQCType has 2 columns, and its actual value is a number. If so, you should use the numeric ID that corresponds to "BAG". For example, if "BAG" corresponds to 37, the line should be

Code: Select all

If ComboQCType = 37 Then

[bmoyer123]

ComboQCType is a drop down combo box (value list) with two choices BAG and VIAL ("BAG";"VIAL"). It is an unbound combobox. Only one column.

[HansV]

Make sure that the Bound Column property of ComboQCType is set to 1, not to 0. If it is set to 1, the line

If ComboQCType = "BAG" Then

should work correctly. I'd have to see (a copy of) the database to investigate why it doesn't...

[bmoyer123]

I deleted combo box. Typed BAG in manually but it enters QV1 etc and not QC1, on module I put BAG in quotes.

[HansV]

Could you create a stripped-down copy of the database without sensitive information, then zip the copy and attach it to a reply?

[HansV]

You replied to the e-mail notification of my reply instead of to the reply in this thread. In the future, please reply in the forum itself...

But it was helpful. Your code in Module1a is

Code: Select all

If QCType = "BAG" Then

Since the code is in a separate module, not in the form module, QCType does not refer to the form. You have to use

Code: Select all

If Form_frmAddDivisions.QCType = "BAG" Then

That way, it refers to the combo box on the form.

[bmoyer123]

Thank you that worked.