Data Passing from one form to another form

[Sandhya1988]

Hello, Can anyone solve my issue

Form: SB_1
Form: Add_NewRelation s

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_NewRelation s for New Customer details are needed to update into Form: Add_NewRelation s.

So that VBA code is working well. But some of exist customer details are no need to update into Form: Add_NewRelation s and only allow for New Customer details.

So I have added an If statement with Dcount in the code for exist customers no need to add in the Form: Add_NewRelation s. So If Statement with Dcount is didn’t working… how to correction from DCount?? When i was used Dcount function then whole VBA code is not working…

I have attached the dB file can anyone please see once VBA code and correction it.

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

Thanks,
Sandhya.

[zmbd]

Sorry your question was missed... it occasionally happens on busy posting days.

Have you found a solution to your issue or do you still require some assistance?

Just a few things that occur to me at first glance:
+ the use of the keyword "Cancel" as a variable. Cancel does not work in an "OnClick" event if that is your intent.

+ The most likely reason that your if-then conditional is not working as expected is because the dcount() is returning a result greater than zero. This would then imply that the sqlString at line 6 is not returning the condition that you expected.

To trouble shoot this, place the following code immediately following line 6:

debug.print "strCriteria= " & strCriteria

Run your code both for a new customer and an existing customer.
Press <ctrl><g> this will open the immediates window where the resolved string will be shown for each run. Check these for the expected result as criteria for yor Dlookup(). You might want to copy and paste the results back to this thread for a proofread, please be sure to use the [CODE/] format around the string... it is required for all script (VBA, SQL, etc...)

+ I noted that you have not declared your variables. I suspect that the option explicit isn't set. (Before Posting (VBA or SQL) Code - Section A(1) ) having this set and then performing the debug/compile will help you find a large number of errors. I can PM you my boilerplate that has links to some, imho, very useful tutorials and information for new Access developers if you would like.

+ Finally your

DoCmd.OpenForm "Add_NewRelations", acNormal, , , , acWindowNormal

can be modified so that it opens for new record edition only as follows:

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

Normally I would use the open arguments to pass the values to this form; however, one can set them as you are doing in lines 19 thru 25; however, as I'm sure you'll notice, doing so creates the new record...

You might find our insights article covering Database Normalization and Table Structures. an interesting read.