By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Data Passing from one form to another form

P: 30
Hello, Can anyone solve my issue

Form: SB_1
Form: Add_NewRelations

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 into Form: Add_NewRelations.

So that VBA code is working well. But some of exist customer details are no need to update into Form: Add_NewRelations 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_NewRelations. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Button_Click()
  2. On Error GoTo ErrorHandler
  3. 'Me.Refresh
  5. Dim strCriteria As String
  6. strCriteria = "CIDCustomer = '" & Trim(Trim(Me!RID) & " " & Trim(Me!RName)) & "'"
  8.  'If DCount("*", "CIDCustomer", "RelationsQry") > 0 Then
  9.  'Cancel = True
  10.  'Else
  12. If DCount("*", "RelationsQry", strCriteria) > 0 Then
  13. Cancel = True
  14. Else
  16.    'If Not IsNull([Customer]) Then
  17.    'Me.Visible = False
  18.    DoCmd.OpenForm "Add_NewRelations", acNormal, , , , acWindowNormal
  19.    Forms![Add_NewRelations].Form.RID = Me.CID2
  20.    Forms![Add_NewRelations].Form.RName = Me.Customer
  21.    Forms![Add_NewRelations].Form.RType.Value = "Customer"
  22.    Forms![Add_NewRelations].Form.Address = Me.Address
  23.    Forms![Add_NewRelations].Form.TINNumber = Me.TINNumber
  24.    Forms![Add_NewRelations].Form.TownVLG = Me.TownVLG
  25.    Forms![Add_NewRelations].Form.RName.SetFocus
  26.    'Forms![Add_NewRelations].Visible = False
  27.    'DoCmd.Close
  29. End If
  31. ErrorHandler:
  32. End Sub

Attached Files
File Type: zip SB1 (813.0 KB, 40 views)
May 26 '15 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 5K+
P: 5,397
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Add_NewRelations", acNormal, , , , acWindowNormal
can be modified so that it opens for new record edition only as follows:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 27 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.