Issue with text box search. If match found, display form. If not, add new.

[BouwmeesterL]

Hello,

I am having difficulty with Access VBA code. I am using Access 2010 (from work)/2013 (from home). I am creating a database that analyzes the nutritional quality of prepackaged foods for my place of employment, a local food bank.

Users enter a products UPC Code (which I use as the Primary Key). What I would like it to do, is if the product has been previously entered, the Product Form will open. If it is not yet in the database, I would like it to go to a new form with the UPC Code, which was previously entered to search, already populated.

I am able to get these to work individually, but as soon as I try to place them together, it fails. I have included the code that has worked below.

The following code opens a matching record to the appropriate form.

Private Sub cmdOK_Click()
    DoCmd.OpenForm "frmProduct", acNormal, "", "[UPCCode]=[Forms]![frmUPCCodeSearch]![txtUPCCodeSearch]", acReadOnly, acNormal
    DoCmd.Close acForm, "frmUPCCodeSearch"
End Sub
 

Then this code opens a new form with the UPC Code populated.

Private Sub cmdSearchOpenForm_Click()
    DoCmd.OpenForm "frmProduct", acNormal, acFormAdd, acWindowNormal
        Forms!frmProduct!UPCCode = Me.txtUPCSearch
    DoCmd.Close acForm, "frmUPCSearch"
End Sub
 

This is my first post here, so I apologize if I am missing anything, information or posting process. Please feel free to ask any questions or leave any comments. Any help on this would be amazing.

Thank you.

L

[jimatqsi]

BouwmeesterL,
Welcome to Bytes.com.

You are not telling anything about the nature of the failure. What is that happens that should not happen, or what fails to happen that should?

You are closing two different forms, as seen in lines 3 of the first code block and line 4 of the second. Is that what you meant to do? Why close the calling form at all?

Jim

[twinnyfo]

I am a bit confused, as Jim is, but I would approach a solution by first searching for the UPC code on your First Form. Then, if the UPC is found, open the Product Form, filtered by the UPC, if it is not found, then open the Product Form, but go to a new record. All this could be done from the same cmdOK_Click procedure.

[BouwmeesterL]

I apologize for the lack of information and would like to thank you both for the quick response.

So twinnyfo, that is precisely what I am having difficulty doing. I would like to have both codes work from the same cmdOK_Click(), but have not been able to get them to work correctly which is why I separated the two for this purpose.

jimatqi, for my database. From the Home menu, you have the option to search for a UPC Code. Once that is selected the Search form (frmUPCCodeSearch) opens. Once the UPC Code has been entered and searched for, yes, I would like that form (frmUPCCodeSearch) to close to remove unneeded forms from the window.

When I combine the two codes, it has

A. Correctly open the Product form (frmProduct) with the matching record open. (which is precisely what I would like it to do).

B. When entering a product that is currently not entered in the database the Product form opens with only the header and footer visible. All other text boxes (where an individual enters in the nutritional quality information) is missing.

I have been playing around with it this morning and believe I may am on the way to a solution with a DCount syntax

Here it is...

Private Sub cmdOK_Click()
Private Sub cmdOK_Click()
  If DCount("UPCCode", "tblProduct", "UPCCode = '" & Me.txtUPCCodeSearch & "'") > 0 Then 'Add new product
        DoCmd.OpenForm "frmProduct", acNormal, acFormAdd, acWindowNormal
            Forms!frmProduct!UPCCode = Me.txtUPCCodeSearch
        DoCmd.Close acForm, "frmUPCCodeSearch"
 
    Else
        DoCmd.OpenForm "frmProduct", acNormal, "", "[UPCCode]=[Forms]![frmUPCCodeSearch]![txtUPCCodeSearch]", acReadOnly, acNormal
        DoCmd.Close acForm, "frmUPCCodeSearch"
    End If
End Sub
 

When I try this. If there is a matching record, it goes to the Product form, all the text boxes are visible, but empty, with the exception of the UPC Code which is filled. (This is what I would like it to do if there is no matching record.)

If there is no matching record, the same thing that I previously mentioned with the no matching record occurs. Only the header and footer are visible.

I hope this information is more descriptive. Once again, any other questions, please feel free to ask. This has been driving me crazy.

Thank you.

[twinnyfo]

B. When entering a product that is currently not entered in the database the Product form opens with only the header and footer visible. All other text boxes (where an individual enters in the nutritional quality information) is missing.

Check to make sure that your form's AllowAdditions Property is set to Yes

[jforbes]

I think your OpenForm call is missing some arguments. They are blank, but still need to be there for the arguments to be in the correct order. I think line 4 should be:

DoCmd.OpenForm "frmProduct", acNormal, , , acFormAdd,acWindowNormal

[zmbd]

Jforbs is correct, when using the shortended form, the order of the entries is vital.

This is why I prefer using named augments for all but the simplest of methods (think Msgbox) and suggest that most new programmers use the same method.

DoCmd.OpenForm _
        FormName:="frmProduct", _
        View:=acNormal, _
        DataMode:=acFormAdd, _
        WindowMode:=acWindowNormal
 

You can scramble the order of "FormName:=", "View:=", "DataMode:=", and "WindowMode:=" and it wouldn't matter... so long as the ":=" is used and the correct information is supplied.

[BouwmeesterL]

Thank you for your help, but I could not get that to work. It displayed a compile error over View: "Expected: End of Statement".

After speaking with some other individuals, I was informed that something so simple would not accomplish what I am trying to automate. This was sent to me.

The syntax is completely incorrect, but this is the gist of what you need.
 
i = count of rows in the table
searchResult
enteredUPC = the upc the user typed in
UPCColumn = the column in the table that stores the UPC codes already entered
 
For 1 to i (where i is a count of the rows in the tables)
  If enteredUPC = UPCColumn.i then (UPCColumn.i is the Column in the table that stores the UPC Code and i represent the current row to compare to the user-entered UPC)
     searchResult = True
     exit
  Else
  End if
Next i
 
If searchResult = True
  LoadForm-Results
Else
  LoadForm-EnterInformation
End if
 
 

From that, the following is what I have come up with:

Private Sub cmdOK_Click()
 
    Dim dbs As Database
    Dim rst As Recordset
    Dim a As Integer
    Dim i As Integer
    Dim Check As Long
    Dim bResult As Boolean
    Dim sUPCSearch As String
    Dim sCodeKey As String
 
    'Get the database and recordset
    Set dbs = CurrentDb
    Set iCount = DCount("*", tblProduct)
    sUPCSearch = [Forms]![frmUPCCodeSearch]![txtUPCCodeSearch]
    sCodeKey = (tblProduct.UPCCode)
 
    For Check = 1 To iCount
        If sUPCSearch = [tblProduct]![UPCCode].iCount Then
        'UPCCode in tblProducts and UBound represent the current row to compare to the user-entered UPC)
            bResult = True
        Else
        End If
 
    Next
 
    If bResult = True Then
        DoCmd.OpenForm "frmProduct", acNormal, "", "[UPCCode]=[Forms]![frmUPCCodeSearch]![txtUPCCodeSearch]", acReadOnly, acNormal
        DoCmd.Close acForm, "frmUPCCodeSearch"
    Else
        DoCmd.OpenForm "frmProduct", acNormal, acFormAdd, acWindowNormal
            Forms!frmProduct!UPCCode = Forms!frmUPCCodeSearch!txtUPCCodeSearch
        DoCmd.Close acForm, "frmUPCCodeSearch"
    End If
 
End Sub
 

It is still having issues with the DCount. I used that because I couldn't figure out any other ways to get a dynamic count on the records in the table.

I apologize, but I feel like I am going crazy here with the amount of different ways I am trying to make this work. And for all I know, I am now just completely over-complicating the process.

Thanks.

[jforbes]

I think your buddies may mean well, but I think they are leading you away from your solution. I also think what they are recommending is more of a .NET solution. Since you are in MS Access, you can shortcut some of the things they are doing.

I created a project with a table and a couple forms using your names an pasted the your code into it and there were a few things I had to fix to get it working, but you were really close:

• Reversed the operator on the DCount evaluation.
• Tweaked the Where clause to be a little more straight forward and to use Single Quotes for the UPC. If your UPC is a Number, you'll need to remove the Single Quotes.
• Added the blank Arguments for the DoCmd.Open
• Changed the references to the TextBox to use the Value of the TextBox

Private Sub cmdOK_Click()
    If DCount("UPCCode", "tblProduct", "UPCCode = '" & Me.txtUPCCodeSearch.Value & "'") = 0 Then
        'Add New Product
        DoCmd.OpenForm "frmProduct", acNormal, , , acFormAdd, acWindowNormal
        Forms!frmProduct!UPCCode = Me.txtUPCCodeSearch.Value
        DoCmd.Close acForm, "frmUPCCodeSearch"
    Else
        ' Display Existing Product
        DoCmd.OpenForm "frmProduct", acNormal, , "[UPCCode]='" & Me.txtUPCCodeSearch.Value & "'", acReadOnly, acNormal
        DoCmd.Close acForm, "frmUPCCodeSearch"
    End If
End Sub
 

Let us know how things go.

[Rabbit]

The key takeaway from jforbes' modified code is that you reversed your code in post #4. You were checking if the count was greater than 0, then add a new record. Otherwise, open the record. But that's backwards, if the count is greater than 0, then the record exists so you shouldn't be adding a new record.

[BouwmeesterL]

Wonderful! Thank you so much for the time you have all taken. It works perfectly.


Thank you.