If record in subform exists then...

[epardo87]

Hello there,

So I have this button PN2001Btn that represents a product, this is inside a form POS where new orders are recorded. There is a subform called SalesDetailsSF which is Child6 inside the POS form creating the list of products that the new sale order will have. This button already fills a new record with the predetermined product and it also fills up the corresponding price.

What I need is that if that product was already there, then it sums the QTY up another number, but if it's not there then to continue adding a new record, I have tried a couple of things without success...


this is the code I have so far, but it only adds new records on each click, you can see the image of the form below so that you get an idea of what it is about (the button would be one of the bowls filling up the subform on the left, parent and child forms are connected by the SO

Private Sub PN2001Btn_Click()

    Dim PCode As Integer
    Dim LPrice As Variant
    Dim PPrice As Variant
    Dim ProdName As Variant
    
    PCode = 2001


             LPrice = DLookup("[PrecioL]", "[Precios]", "[PN]=" & PCode)
             PPrice = DLookup("[PrecioP]", "[Precios]", "[PN]=" & PCode)
             ProdName = DLookup("[Producto]", "[Products]", "[PN]=" & PCode)
                     
             Me.Child6.SetFocus
             DoCmd.GoToRecord , , acNewRec
             Me.Child6.Form.Producto = ProdName
             
             If Me.SaleType = "Local" Then
                 Me.Child6.Form.Precio = LPrice
             Else
                 Me.Child6.Form.Precio = PPrice
             End If


End Sub

[Welshgasman]

You would need to walk through the recordsetclone and see if that product exists.
If it does, increment the amount, if not add that new record.

No need to look up the prices if it exists either?

[Micron]

https://learn.microsoft.com/en-us/of...cmd.findrecord

[epardo87]

You would need to walk through the recordsetclone and see if that product exists.
If it does, increment the amount, if not add that new record.

No need to look up the prices if it exists either?

I'm trying the recodserclone, but i'm kind of stuck, doing some tests, I have the code below, it successfully detects if the SO exists, but i'm kind of stuck, what I need it to find is a record with that SO and that PN, but i dont know hot to write that, here are the details again:
Form SOP contains a button PN2001Btn which on click is going to do the following code. in order to solve my problem I just need it to successfully find if there is already a record within the subform which has that same SO and that PN(subform is Child6 inside the form and it containsa form called SalesDetailsSB)

    Dim rst As Recordset    Dim SOSearch As String
    
    Set rst = Me.RecordsetClone
    SOSearch = Str(Me.SO)
    rst.FindFirst "SO = " & SOSearch
        If rst.NoMatch Then
            MsgBox "not found"
        Else
            MsgBox "found"
        End If
    rst.Close

[Micron]

What is the data type of SO? My guess is text because you can't start a number type with 0. Thus you must delimit text based variables passed as parameters (and date/time values, but with # for those). Try
"SO = ' " & SOSearch & " ' "
Remove the spaces between the quotes. I only wrote them so you could see the single quote marks.

EDIT - then again, you say what you posted works to find the SO so I think you can ignore the above.
If FindFirst produces a match then that record becomes the current record. Since you're using rs clone I think you'd have to bookmark it, then return to the form recordset, go to that record (make it current by using the bookmark) and up the count by one. Note that in order for any of this to work you must not allow the duplicate to be entered (saved as a record), otherwise the duplicate will remain. You should always show what event your code is in otherwise we're asking or suggesting when it may already be correct. Sorry if I missed that info, but I'd say the event has to be form BeforeUpdate.

[epardo87]

What is the data type of SO? My guess is text because you can't start a number type with 0. Thus you must delimit text based variables passed as parameters (and date/time values, but with # for those). Try
"SO = ' " & SOSearch & " ' "
Remove the spaces between the quotes. I only wrote them so you could see the single quote marks.

EDIT - then again, you say what you posted works to find the SO so I think you can ignore the above.

It is an autonumber with formatting to 4 digits

[Micron]

You did not do this directly in table design, correct (because I don't think you can do so to autonumber field)? You did it somewhere else and used the Format function? Then its data type would be text. However, not sure if this all matters because you say the lookup works. Note that I made changes to my prior post which you may not have noticed.

[epardo87]

You did not do this directly in table design, correct (because I don't think you can do so to autonumber field)? You did it somewhere else and used the Format function? Then its data type would be text. However, not sure if this all matters because you say the lookup works. Note that I made changes to my prior post which you may not have noticed.

Yes, originally there is table Sales, which contains the autonumber, so that SO field in the parent form is directly pulled from there. the thing is, i made the code find the SO within the subform but I havent been able to find the product with that SO

[epardo87]

I consider myself a quick learner but I think specifically this issue is an advanced level I just can't seem to understand as easy

[Micron]

Could be a lack of thorough understanding of what's what on our part. Consider copying your db, compact/repair the copy, zip it and posting that. Although it appears to work, you cannot paste files or pics into a post. You can remove everything from the copy that's not required for this issue - just make sure there's enough there to replicate the process and problem. See "how to attach files" at the top of this page if you need it. There is also a way to edit field data to "cloak" it if you need to - here's one version of that in these forums: https://www.accessforums.net/showthread.php?t=77482

[orange]

I agree with micron - it would be better for communication and solution if you could post what you have so everyone is dealing with a common issue. The evolving database and any documentation and design materials would be most helpful.
As with most databases with issues ---getting a working copy with clear instructions to the readers is often an efficient path to solving.

[epardo87]

Could be a lack of thorough understanding of what's what on our part. Consider copying your db, compact/repair the copy, zip it and posting that. Although it appears to work, you cannot paste files or pics into a post. You can remove everything from the copy that's not required for this issue - just make sure there's enough there to replicate the process and problem. See "how to attach files" at the top of this page if you need it. There is also a way to edit field data to "cloak" it if you need to - here's one version of that in these forums: https://www.accessforums.net/showthread.php?t=77482

Ok, I've deleted all extra data just to leave what works for this, the form POS is the one with the button, I have only the first working button, you'll find code in the SaleType afterupdate event and the PN2001Btn on click event

[Vstar920AZ♣]

If you are just wanting to know if a record "exists", I would use If (Dcount("[Field]","Table" i.e. the subForm recordsource, Criteria Here "[SO_Nbr]=" & Variable & "[ProductCode]='" & variable "'" . . .). >0 Then . . . If you get > 0 then there is a record.
If your recordset is very large, you may need to write the query in code, and do a recordset operation. https://www.access-programmers.co.uk...rdsets.225340/
But, if the question is "Exists or not" that is essentially a yes no answer which should be doable by the Dcount function.

[epardo87]

If you are just wanting to know if a record "exists", I would use If (Dcount("[Field]","Table" i.e. the subForm recordsource, Criteria Here "[SO_Nbr]=" & Variable & "[ProductCode]='" & variable "'" . . .). >0 Then . . . If you get > 0 then there is a record.
If your recordset is very large, you may need to write the query in code, and do a recordset operation. https://www.access-programmers.co.uk...rdsets.225340/
But, if the question is "Exists or not" that is essentially a yes no answer which should be doable by the Dcount function.

I'm trying this, but it has an error on the way im writting the DCount line, I dont know if thats the correct way to have if search within the subform:

    Dim PCode As Integer    Dim LPrice As Variant
    Dim PPrice As Variant
    Dim ProdName As Variant
            
    PCode = 2001

    ProdName = DLookup("[Producto]", "[Products]", "[PN]=" & PCode)
             
    If (DCount("[Producto]", "[Child6]", ProdName)) > 0 Then
        MsgBox "found"
    Else
        MsgBox "doesnt exist"
    End If

[Vstar920AZ♣]

ProdName = DLookup("[Producto]", "[Products]", "[PN]=" & PCode)

If DCount("[Producto]", "[Child6]", "[Producto]='" & ProdName & "'") > 0 Then 'Product name value will not be available inside the quotes, it must be in the base code. Type it in lower case, if it snaps back to your capitalization, then the code can see it.
MsgBox "found"
Else
MsgBox "doesnt exist"
End If