Issue with Data passing from one form to another form in MS Access VBA

Question

Can anyone solve my issue

Form: SB_1 (this is a Customer Invoice creation form)

Form: Add_NewRelations (this is a Customer details adding form)

I have used a VBA code in Form: SB_1 on a cmd (Button) for Data passing from one form to another form of Add_NewRelations for New Customer details are needed to update.

So that VBA working is good as per Code Tag. But some of exist customer details are no need to update into Form: Add_NewRelations. So in this case i have added IF Statement with DCount in the VBA code.

When i added If statement with DCount then whole VBA is didn't working... i think DCount is wrong... how can i correction it??

Can anyone please replay how to solve this problem??

Private Sub Button_Click()
On Error GoTo ErrorHandler
'Me.Refresh

Dim strCriteria As String
strCriteria = "CIDCustomer = '" & Trim(Trim(Me!RID) & " " & Trim(Me!RName)) & "'"

 'If DCount("*", "CIDCustomer", "RelationsQry") > 0 Then
 'Cancel = True
 'Else

If DCount("*", "RelationsQry", strCriteria) > 0 Then
Cancel = True
Else

   'If Not IsNull([Customer]) Then
   'Me.Visible = False
   DoCmd.OpenForm "Add_NewRelations", acNormal, , , , acWindowNormal
   Forms![Add_NewRelations].Form.RID = Me.CID2
   Forms![Add_NewRelations].Form.RName = Me.Customer
   Forms![Add_NewRelations].Form.RType.Value = "Customer"
   Forms![Add_NewRelations].Form.Address = Me.Address
   Forms![Add_NewRelations].Form.TINNumber = Me.TINNumber
   Forms![Add_NewRelations].Form.TownVLG = Me.TownVLG
   Forms![Add_NewRelations].Form.RName.SetFocus
   'Forms![Add_NewRelations].Visible = False
   'DoCmd.Close

End If

ErrorHandler:
End Sub

gavin downie gavin downie · Accepted Answer · 2015-06-11T16:07:27

To pass the quotes into the SQL you need to escape them, and you need additional single quotes as well for the elements of the criteria.

Instead of:

strCriteria = "CIDCustomer = '" & Trim(Trim(Me!RID) & " " & Trim(Me!RName)) & "'"

try

strCriteria = "" "CIDCustomer = '" & Trim(Trim(Me!RID) & "' & " " & '" Trim(Me!RName)) & "'" ""

Issue with Data passing from one form to another form in MS Access VBA

1 Answers