Solved Transferring data from unbound MS Access Form to another form

[nector]

This scenario is based on a POS touch screen where you cannot see any keyboard for data entry, now in MS Access we built a Keyboard unbound form to assist in capturing data. The reason why we did it that way is that there are some situations when the scanner fails to scan the barcode, and so the option or method used is to enter the barcode manually. That is okay if there is a keyboard but without a keyboard it’s almost impossible to do it.

I have assisted the users to transfer the captured data into the required scanning control using this method below, but it does not go into the subform datasheet rows, that is where my problem is, any idea here will be highly appreciated.

Private Sub BtnEnterKey_Click()
Forms!frmCustomerInvoice!txtProductCode.Value = Me.Calc
End Sub

To appreciate the question above kindly get the database attached and simply go to properties and check the unblock field then it will open. For the keyboard to work simply compile the code.

 

[arnelgp]

try this, make sure you read the VBA code.
you also remove "unnecessary" keys on your Keyboard POS, since it is mainly used
for manual entry of Product Code (no math involved).

 

[nector]

try this, make sure you read the VBA code.
you also remove "unnecessary" keys on your Keyboard POS, since it is mainly used
for manual entry of Product Code (no math involved).

Many thanks arnelgp exactly that is what I was looking for

 

[nector]

try this, make sure you read the VBA code.
you also remove "unnecessary" keys on your Keyboard POS, since it is mainly used
for manual entry of Product Code (no math involved).

The demo db works ok but the same concept when I transfer it to the actual POS I'm getting an on the calculator code even after changing the external sub to public.

Here are the changes I did make

Public Sub txtProductCode_AfterUpdate()
Dim lngProdID As Long
    
    If Not (IsNull(mID)) Then
        DoCmd.GoToControl "sfrmPosLineDetails Subform"
              
        With Me![sfrmPosLineDetails Subform].Form
            ![ProductID] = mID
            ![QtySold] = 1
            ![ProductName] = DLookup("ProductName", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![TaxClassA] = DLookup("vatCatCd", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![SellingPrice] = DLookup("dftPrc", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![Tax] = DLookup("VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![RRP] = 0
            ![ItemesID] = DCount("[QtySold]", "tblPosLineDetails", "[ItemSoldID] =" & Me.txtDcounting)
        End With
    DoCmd.GoToRecord , , acNewRec
        
    End If
        
    Me.txtProductCode = Null
    Me.txtProductCode.SetFocus
End Sub


Public Sub txtProductCode_BeforeUpdate(Cancel As Integer)
If IsNull(Me.DocID) Then
Beep
MsgBox "Please select the document type now", vbCritical, "You have not selected the document type"
Cancel = True
Exit Sub
End If

bolValid = True
    If Not IsNull(Me.txtProductCode) Then
        ' now check if it is valid
        mID = DLookup("ProductID", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
        
        bolValid = (IsNull(mID) = False)
        Cancel = Not bolValid
        If Cancel Then
            MsgBox "Barcode is not on product table!"
        End If
    
    End If

End Sub

 

[MajP]

This is why you should not use bang but dot notation wherever possible and do not EVER EVER put spaces in any name of anything.
Are you sure you have a control of that name?
Me![sfrmPosLineDetails Subform]
but this would have told you if using dot and a normal name.
me.sfrmProslineDetails

Why would you even name it that with subform at the end if you start it with sFrm?

 

[arnelgp]

change the After_Update to:

' arnelgp
' change this to Public so it can be called from external
Public Sub txtProductCode_AfterUpdate()
    
    Dim lngProdID As Long
    Dim strReturn As String, var As Variant
    If Not (IsNull(mID)) Then
        'remove this
        'DoCmd.GoToControl "sfrmPosLineDetails Subform"
              
        With Me![sfrmPosLineDetails Subform].Form
            'add this
            .Recordset.AddNew
            
            ![ProductID] = mID
            ![QtySold] = 1
            
            ' just use 1 Dlookup
            strReturn = DLookup("ProductName & '|' & vatCatCd & '|' & dftPrc & '|' & VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            var = Split(strReturn, "|")
            
            ![ProductName] = var(0)
            ![TaxClassA] = var(1)
            ![SellingPrice] = var(2)
            ![Tax] = var(3)
            
            '![ProductName] = DLookup("ProductName", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![TaxClassA] = DLookup("vatCatCd", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![SellingPrice] = DLookup("dftPrc", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![Tax] = DLookup("VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            
            ![RRP] = 0
            ![ItemesID] = DCount("[QtySold]", "tblPosLineDetails", "[ItemSoldID] =" & Me.txtDcounting)
        End With
        'remove this
        'DoCmd.GoToRecord , , acNewRec
        
    End If
    
    Me.txtProductCode = Null

End Sub

 

[nector]

change the After_Update to:

' arnelgp
' change this to Public so it can be called from external
Public Sub txtProductCode_AfterUpdate()
   
    Dim lngProdID As Long
    Dim strReturn As String, var As Variant
    If Not (IsNull(mID)) Then
        'remove this
        'DoCmd.GoToControl "sfrmPosLineDetails Subform"
             
        With Me![sfrmPosLineDetails Subform].Form
            'add this
            .Recordset.AddNew
           
            ![ProductID] = mID
            ![QtySold] = 1
           
            ' just use 1 Dlookup
            strReturn = DLookup("ProductName & '|' & vatCatCd & '|' & dftPrc & '|' & VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            var = Split(strReturn, "|")
           
            ![ProductName] = var(0)
            ![TaxClassA] = var(1)
            ![SellingPrice] = var(2)
            ![Tax] = var(3)
           
            '![ProductName] = DLookup("ProductName", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![TaxClassA] = DLookup("vatCatCd", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![SellingPrice] = DLookup("dftPrc", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![Tax] = DLookup("VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
           
            ![RRP] = 0
            ![ItemesID] = DCount("[QtySold]", "tblPosLineDetails", "[ItemSoldID] =" & Me.txtDcounting)
        End With
        'remove this
        'DoCmd.GoToRecord , , acNewRec
       
    End If
   
    Me.txtProductCode = Null

End Sub

Here is an error Im getting

I'm not sure how to change it

 

[nector]

This is why you should not use bang but dot notation wherever possible and do not EVER EVER put spaces in any name of anything.
Are you sure you have a control of that name?
Me![sfrmPosLineDetails Subform]
but this would have told you if using dot and a normal name.
me.sfrmProslineDetails

Why would you even name it that with subform at the end if you start it with sFrm?

Let me check properly

 

[arnelgp]

upload your new db.
the subform name might have been changed.

 

[nector]

upload your new db.
the subform name might have been changed.

Ok give some time let get it orgaizized

 

[nector]

upload your new db.
the subform name might have been changed.

Below is the attached DB

 

[nector]

To appreciate the question above kindly get the database attached and simply go to properties and check the unblock field then it will open. For the keyboard to work simply compile the code.

 

[arnelgp]

try and test your db.

 

[nector]

There is still an error when you try to capture the second item on the addnew

 

[arnelgp]

i don't get any error on the new code.

 

[nector]

i don't get any error on the new code.

Just use this box to capture the second line you will get an error , first no error second line you have an error

Example

(1) 410000100
(2) 410000101
(3) 410000102
If you use the form to capture yes the code it works ok but the scan box is where the scanner scan , the form is meant for manual capturing only where keyboards are not available while the box above is the main one used for scanner capturing. When the scanner fail that is when you call a form which is now working but the scanner box is no longer accepting many lines

 

[arnelgp]

i save the record immediately after each scan.

 

[nector]

i save the record immediately after each scan.

Still an error here

 

[nector]

i save the record immediately after each scan.

Many thanks a lot sir, you just opened my mind I think just taking your idea you wanted to say this, and it works ok

Public Sub txtProductCode_AfterUpdate()
Dim lngProdID As Long
Dim sReturn$, varValue As Variant
    If Not (IsNull(mID)) Then
        'DoCmd.GoToControl "sfrmPosLineDetails Subform"
             
        With Me![sfrmPosLineDetails Subform].Form
            .Recordset.AddNew
           
            ![ProductID] = mID
            ![QtySold] = 1
           
            sReturn = DLookup("ProductName & '|' & vatCatCd & '|' & dftPrc & '|' & VAT", _
                            "tblProducts", _
                            "BarCode = '" & Me.txtProductCode & "'")
            varValue = Split(sReturn, "|")
           
            ![ProductName] = varValue(0)
            ![TaxClassA] = varValue(1)
            ![SellingPrice] = varValue(2)
            ![Tax] = varValue(3)
           
            '![ProductName] = DLookup("ProductName", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![TaxClassA] = DLookup("vatCatCd", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![SellingPrice] = DLookup("dftPrc", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            '![Tax] = DLookup("VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
           
            ![RRP] = 0
            ![ItemesID] = DCount("[QtySold]", "tblPosLineDetails", "[ItemSoldID] =" & Me.txtDcounting)
           
            'immediately save the record
            'DoCmd.RunCommand acCmdSaveRecord
            If Me.Dirty = True Then
        ' Save the current record
        Me.Dirty = False
    End If
        End With
    'DoCmd.GoToRecord , , acNewRec
       
    End If
    Me.sfrmPosLineDetails_Subform.Requery
       
    Me.txtProductCode = Null
    Me.txtProductCode.SetFocus
End Sub