To display data using query in a form.

[Malavika1990]

hai all,
Please help me, i was trying to rerive data using query and that data has to display in the form. But the problem is data is, data which is retrived by the query is not coming to the form. please help me.

strSQL = strSQL + " Where (CustomerTravelTrns.EmployeeTravelTrnsId = ScheduledTripTrns.EmployeeTravelTrnsId and EmployeeMstr.R3EmployeeMstrId = ScheduledTripTrns.R3EmployeeMstrId and ScheduledTripTrns.FlightStatus='Arrival' and (CustomerTravelTrns.EmployeeTravelTrnsId)=" & [Forms]![CustomerTravelScheduleForm].EmployeeTravelTrnsId & ");"
 Set rs1 = db.OpenRecordset(strSQL)

This is the Query.

If (Not IsNull(rs1) And Not (rs1.EOF)) Then
  Set tripConfirmObjectFormation.SchPickUp = New SchPickUp
  If (Not IsNull(rs1.Fields(0).Value)) Then
   tripConfirmObjectFormation.SchPickUp.SchArrvPickDate = rs1.Fields(0).Value
  End If

[zmbd]

+Malavika1990+
Please pull your image out of the DOCX file and post it directly.

+

strSQL = strSQL [icode]+[icode] " Where 

1) Change the plus to an ampersand " & "
2) insert

debug.print strSQL
Stop

Following the strSQL line
3) When the code stops and the debugger opens, press <ctrl><G> to open the immediate pane.
4) Copy the SQL text
5) Go back to the main Access window
Ribbon>Create>Queries>QueryDesign
Cancel the show table dialog
Click on the [SQL] Bottom right hand corner ( see image here ) to switch to the SQL view

6) Paste the text you copied earlier
7) Run the query

This should return your result, if not, then you have a malformed string and you can post it here for us to take a look at...

+Your if then statement: YUCK
Better ways of doing this
Allen Browne: VBA Traps: Working with Recordsets

This is air code so there may be a typo or two
This is my preferred method for checking for records.

If Rs1.RecordCount then
(if there is at least one record the recordcount will have 1 record regardless of the actual number of records )
end if

What is this?
Set tripConfirmObjectFormation.SchPickUp = New SchPickUp
Are you instancing a new form?
What is: tripConfirmObjectFormation
What is: SchPickUp

This is a harsh way to reference the field in the recordset
rs1.Fields(0).Value

+ Normally you would: RS![FieldName] to reference a value in the open recordset at the current record

+[Forms]![CustomerTravelScheduleForm].EmployeeTravelTrnsId

If you are calling your code from "[CustomerTravelScheduleForm]" then this shortens to:
Me.EmployeeTravelTrnsId if you are referring to the CONTROL value on the form if you are referring to the form's record set then
Me![EmployeeTravelTrnsId]
KEEP IN MIND
the default names for controls on the form are "ugly" as they default to the same name as the field to which they are bound. I cannot stress enough that people rename the control - even it it's something simple such as prefixing txt for a text box: txtEmployeeTravelTrnsId

+ Testing for Null or ZeroLength String (Read here)
I tend to use:

If Not((rs1![FieldName] & "")="") Then
 'your code here
Else
 'Your Code here
End If

Ok, so until we verify that your SQL is actually returning records this should be a good start

[NeoPa]

If the data is not showing on the form you need to follow these steps :

1. Determine the exact SQL of the RecordSource (We also may need to see this if you still have problems after following these steps carefully).
2. Determine if, using the SQL determined in step #1 above, you see the data you're expecting to see on the form but don't.
3. Determine which Control on the form is the one you would have expected to see the data on.

If you're still having problems then lets see the results of your steps above and we'll see if we can help.

[Malavika1990]

@ZMbd
Thank you for the reply, while trying to run the query in the above mentioned process, it is asking for parameter values. why it is asking like that? did i have done any mistake?

[zmbd]

Malavika1990:
If the query is asking you for information then the SQL is most likely malformed.

Post your SQL from the query you tried to run, please use the [CODE/] formatting in the tool bar on your SQL string.

[Malavika1990]

SELECT DISTINCT ScheduledTripTrns.PickupDate
, ScheduledTripTrns.PickupTime
, ScheduledTripTrns.[Pickup Location]
, ScheduledTripTrns.[Dropoff Location]
, ScheduledTripTrns.TotalTripAmount
, EmployeeMstr.R3EmployeeName
, EmployeeMstr.CellPhone

This is the query.

[NeoPa]

Malavika1990:
This is the query.

Seriously? What you've posted isn't workable SQL. It has no FROM clause to match the two different sources you're obviously trying to use.

PS. It's basic manners to respond to all posts where someone is offering their help.

[zmbd]

Malavika1990,
I'm guessing you didn't post the entire resolved SQL string as in post #1 you have a WHERE clause that clearly isn't in the SQL snipit in your last post.

Please follow my instructions as given in Post#2

Also, PLEASE extract your image from the docx file in post#1 and upload it directly. Many of us cannot, or will not, download and open files due to business rules or simple "PC-Hygiene" - if you will extract the image and upload it direct within your post we can push the thumbnail to the message.

You need to help us to help you!

[Malavika1990]

Zmbd
The code is is not breaking to debug mode. it is working fine, but the details are not coming into the outlook mail.Only name of the cutomer is coming remaing detalis are not coming.
i dont know how to extract the image from this question.

[Malavika1990]

Sorry i have posted only half of the sql query.

[NeoPa]

Malavika1990:
Sorry i have posted only half of the sql query.

I would suggest the obvious next step is to correct that by posting the full SQL then, as soon as possible.

[Malavika1990]

Thanks for your response, sql is now working fine.But the data is not displaying in the mail.

[Malavika1990]

SELECT DISTINCT ScheduledTripTrns.PickupDate
  , ScheduledTripTrns.PickupTime
  , ScheduledTripTrns.[Pickup Location]
  , ScheduledTripTrns.[Dropoff Location]
  , ScheduledTripTrns.TotalTripAmount
  , EmployeeMstr.R3EmployeeName
  , EmployeeMstr.CellPhone 
FROM cellPhoneMstr
  , EmployeeMstr, CustomerTravelTrns
  , ScheduledTripTrns
WHERE (
  CustomerTravelTrns.EmployeeTravelTrnsId =
    ScheduledTripTrns.EmployeeTravelTrnsId 
  AND 
    EmployeeMstr.R3EmployeeMstrId =
      ScheduledTripTrns.R3EmployeeMstrId 
  AND 
    (CustomerTravelTrns.EmployeeTravelTrnsId)= " &
      [Forms]!CustomerTravelScheduleForm].EmployeeTravelTrnsId & ")

This is the actual query

[zmbd]

Malavika1990:
Where did this query come from?
It certainly doesn't look like what I was expecting as a resolved string from the VBA code.
For one thing I was expecting that [Forms]!CustomerTravelScheduleForm].EmployeeTravelTrnsId
would have been replaced by the value in the [EmployeeTravelTrnsId] field - if this is a string value then you already have a format issue with the quotes.

Please advise, did you follow my instructions to obtain the resolved string the code is using or did you pull this out of some other source?

[Malavika1990]

Thanks to all for your support now the application is working fine.