423,103 Members | 1,428 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,103 IT Pros & Developers. It's quick & easy.

To display data using query in a form.

P: 13
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.
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL + " Where (CustomerTravelTrns.EmployeeTravelTrnsId = ScheduledTripTrns.EmployeeTravelTrnsId and EmployeeMstr.R3EmployeeMstrId = ScheduledTripTrns.R3EmployeeMstrId and ScheduledTripTrns.FlightStatus='Arrival' and (CustomerTravelTrns.EmployeeTravelTrnsId)=" & [Forms]![CustomerTravelScheduleForm].EmployeeTravelTrnsId & ");"
  2.  Set rs1 = db.OpenRecordset(strSQL)
This is the Query.
Expand|Select|Wrap|Line Numbers
  1. If (Not IsNull(rs1) And Not (rs1.EOF)) Then
  2.   Set tripConfirmObjectFormation.SchPickUp = New SchPickUp
  3.   If (Not IsNull(rs1.Fields(0).Value)) Then
  4.    tripConfirmObjectFormation.SchPickUp.SchArrvPickDate = rs1.Fields(0).Value
  5.   End If
Attached Files
File Type: docx screen shot.docx (18.2 KB, 8 views)
4 Days Ago #1
Share this Question
Share on Google+
13 Replies


zmbd
Expert Mod 5K+
P: 5,202
+Malavika1990+
Please pull your image out of the DOCX file and post it directly.

+
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL [icode]+[icode] " Where 
1) Change the plus to an ampersand " & "
2) insert
Expand|Select|Wrap|Line Numbers
  1. debug.print strSQL
  2. 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.
Expand|Select|Wrap|Line Numbers
  1. If Rs1.RecordCount then
  2. (if there is at least one record the recordcount will have 1 record regardless of the actual number of records )
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. If Not((rs1![FieldName] & "")="") Then
  2.  'your code here
  3. Else
  4.  'Your Code here
  5. End If
Ok, so until we verify that your SQL is actually returning records this should be a good start
4 Days Ago #2

NeoPa
Expert Mod 15k+
P: 30,909
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.
3 Days Ago #3

P: 13
@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?
1 Days ago #4

zmbd
Expert Mod 5K+
P: 5,202
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.
1 Days ago #5

P: 13
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ScheduledTripTrns.PickupDate
  2. , ScheduledTripTrns.PickupTime
  3. , ScheduledTripTrns.[Pickup Location]
  4. , ScheduledTripTrns.[Dropoff Location]
  5. , ScheduledTripTrns.TotalTripAmount
  6. , EmployeeMstr.R3EmployeeName
  7. , EmployeeMstr.CellPhone
This is the query.
1 Days ago #6

NeoPa
Expert Mod 15k+
P: 30,909
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.
1 Days ago #7

zmbd
Expert Mod 5K+
P: 5,202
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!
1 Days ago #8

P: 13
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.
12 Hours Ago #9

P: 13
Sorry i have posted only half of the sql query.
12 Hours Ago #10

NeoPa
Expert Mod 15k+
P: 30,909
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.
11 Hours Ago #11

P: 13
Thanks for your response, sql is now working fine.But the data is not displaying in the mail.
11 Hours Ago #12

P: 13
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ScheduledTripTrns.PickupDate
  2.   , ScheduledTripTrns.PickupTime
  3.   , ScheduledTripTrns.[Pickup Location]
  4.   , ScheduledTripTrns.[Dropoff Location]
  5.   , ScheduledTripTrns.TotalTripAmount
  6.   , EmployeeMstr.R3EmployeeName
  7.   , EmployeeMstr.CellPhone 
  8. FROM cellPhoneMstr
  9.   , EmployeeMstr, CustomerTravelTrns
  10.   , ScheduledTripTrns
  11. WHERE (
  12.   CustomerTravelTrns.EmployeeTravelTrnsId =
  13.     ScheduledTripTrns.EmployeeTravelTrnsId 
  14.   AND 
  15.     EmployeeMstr.R3EmployeeMstrId =
  16.       ScheduledTripTrns.R3EmployeeMstrId 
  17.   AND 
  18.     (CustomerTravelTrns.EmployeeTravelTrnsId)= " &
  19.       [Forms]!CustomerTravelScheduleForm].EmployeeTravelTrnsId & ")
This is the actual query
9 Hours Ago #13

zmbd
Expert Mod 5K+
P: 5,202
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?
5 Hours Ago #14

Post your reply

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