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

Not all records are coming into conformation mail.

P: 16
I know that this is a simple question but as a beginer i want your support.In my application if a customer has more than 3 or more trips,only two trips are appering in the confirmation email.
please help me to solve this problem.
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL + " Where (CustomerTravelTrns.EmployeeTravelTrnsId = ScheduledTripTrns.EmployeeTravelTrnsId and EmployeeMstr.R3EmployeeMstrId = ScheduledTripTrns.R3EmployeeMstrId and (CustomerTravelTrns.EmployeeTravelTrnsId)=" & [Forms]![CustomerTravelScheduleForm].EmployeeTravelTrnsId & ");"
  2.  Set rs1 = db.OpenRecordset(strSQL)
  3.  If (Not IsNull(rs1)) Then
  4.     Do Until rs1.EOF
  5.       For i = 0 To rs1.Fields.Count - 1
  6.        If (rs1.Fields(i).SourceField = "Arrival") Then
  7.         If (Not IsNull(rs1.Fields(i).Value)) Then
  8.          Flag = True
  9.         End If
  10.        End If
  11.       Next i
  12.      rs1.MoveNext
  13.  If (Not IsNull(rs1) And Not (rs1.EOF)) Then
  14.   Set tripConfirmObjectFormation.SchPickUp = New SchPickUp
  15.   If (Not IsNull(rs1.Fields(0).Value)) Then
  16.    tripConfirmObjectFormation.SchPickUp.SchArrvPickDate = rs1.Fields(0).Value
  17.   End If
  18.   If (Not IsNull(rs1.Fields(1).Value)) Then
  19.    tripConfirmObjectFormation.SchPickUp.SchArrvPickTime = rs1.Fields(1).Value
  20.   End If
  21.   If (Not IsNull(rs1.Fields(2).Value)) Then
  22.    tripConfirmObjectFormation.SchPickUp.SchArrvPickLocation = rs1.Fields(2).Value
  23.   End If
  24.   If (Not IsNull(rs1.Fields(3).Value)) Then
  25.    tripConfirmObjectFormation.SchPickUp.SchArrvDropLocation = rs1.Fields(3).Value
  26.   End If
  27.   If (Not IsNull(rs1.Fields(4).Value)) Then
  28.    tripConfirmObjectFormation.SchPickUp.SchArrvTotal = rs1.Fields(4).Value
  29.   End If
  30.   If (Not IsNull(rs1.Fields(5).Value)) Then
  31.    tripConfirmObjectFormation.SchPickUp.SchArrvPickDriver = rs1.Fields(5).Value
  32.   End If
  33.   If (Not IsNull(rs1.Fields(6).Value)) Then
  34.    tripConfirmObjectFormation.SchPickUp.SchArrvPickDriverCell = rs1.Fields(6).Value
  35.   End If
  36.  End IfrecordsList.MoveNext
  37.     Loop
  38.  End If
Aug 17 '18 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 5K+
P: 5,397
It's most likely in your string:
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL + 
  2. " Where (CustomerTravelTrns.EmployeeTravelTrnsId = ScheduledTripTrns.EmployeeTravelTrnsId 
  3. and EmployeeMstr.R3EmployeeMstrId = ScheduledTripTrns.R3EmployeeMstrId 
  4. and (CustomerTravelTrns.EmployeeTravelTrnsId)=" 
  5. & [Forms]![CustomerTravelScheduleForm].EmployeeTravelTrnsId & ");"
I'm still advising you, change the + to the &. There's a slight nuance; however, it can be an important one when the compiler hits.

Unless you check that entire resolved string you'll have no idea what records are being pulled.

I walked you thru how to do this here

Resolve the string, check the SQL for proper form and results. Easiest way is by copying the resolved string into the Query Editor in SQL-View.
Aug 17 '18 #2

P: 16
I have checked the sql,it is working fine. But stil i was not getting all the data into the confirmation mail.I have changed "+" to "&".
Aug 20 '18 #3

Expert Mod 5K+
P: 5,397
+ What are you saying here:


When you use the resolved SQL in a stand alone query, the correct records (ALL RECORDS) desired are being shown in the query,


When you use the resolved SQL in the query, it works; however, it is not showing all of the desired records?

+ Please post the SQL as requested - help us to help you :)
Aug 20 '18 #4

Post your reply

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