473,394 Members | 1,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Print Selected records from DropDownBox

DD
I have a mainform with a subform.
The main form has a dropdown box "chooseMonth", in the afterupdate event
i requery the subform so all records with the same date are viewed.
Now i only want to print the selected records of the selected month
Can any one advise on the below


Below is code i am trying to adapt (Thanks Don)
1.This first part is causing me problems so i have deleted it as it
strikes me that it is looking for a listbox and i have a form with a
subform please correct me if i am wrong as an error appears with the
VarItm.
2.
The other part of the code is what i am working on, my problem is that
Me.choosemonth is giving me an error 3070 does not recognise 200403 as
a valid field .
Where do i get this number from? qryGSTRecieved
PaidMonth: Format([paidDate],"yyyymm ")
This is used in the dropdown box Me.ChooseMonth
--------------------------------------------------------------------------------
Deleted this

Dim ctl As Control
Dim varItm As Variant

Dim strSelected As String

Set ctl = Me.lstTransType
For Each varItm In ctl.ItemsSelected

If Len(strSelected) > 0 Then
strSelected = strSelected & ", " & chr$(39) &
ctl.ItemData(varItm) & chr$(39)
Else
strSelected = chr$(39) & ctl.ItemData(varItm) & chr$(39)
End If

Next varItm

'Debug.Print strSelected
--------------------------------------------------------------------------------
Using this

MySql = "" 'Clear the string variable

'Construct the MAIN SQL statement here
MySql = MySql & "SELECT qryGSTRecieved.* FROM qryGSTRecieved "

'Now test for and add the WHERE portion here
If Len(strSelected) > 0 And InStr(1, strSelected, "All") = 0 Then
MySql = MySql & "WHERE (((qryGSTRecieved.PaidDate)In("
MySql = MySql & strSelected
MySql = MySql & "))) "
End If

'Again, test for and add the ORDER BY here.
If Not IsNull(Me.chooseMonth) Then
MySql = MySql & "ORDER BY "
MySql = MySql & Me.chooseMonth
End If

'Finally, "close out" the SQL string using the semi-colon
MySql = MySql & "; "

'Check the resulting SQL string.
'If an error is displayed, you can copy the SQL string from the debug
window
'into a new query in order to help determine why it isn't working
'Debug.Print MySQL

'Setting the subform's Recordsource here automatically updates the
display.
'No need to requery the subform.
Me.fsubGSTRecieved.Form.RecordSource = MySql
Set ctl = Nothing

End Sub
============================
Nov 12 '05 #1
3 2628
Hi David,

OK ... try this, I think you're on the right track:
Be sure to have a look at that web link and modify [chooseMonth]'s RowSource
property to include the "(All)" thing.... right?
If all of this still doesn't work, post the RowSource of you combo-box too.

Note that I used -- Format(qryGSTRecieved.PaidDate,"yyyymm" -- in the WHERE
section below. If you're going to have the SQL find data, it has to be in
the same format as the combo-box.

*************************************************
Private Sub chooseMonth_AfterUpdate() 'Right?
' "Add All to List" Courtesy: Dev Ashish
' http://www.mvps.org/access/forms/frm0043.htm
' ALWAYS a good idea to pay homage to those we learn and borrow from :)
' Also makes a great reminder for "How the heck did I do that last time?"

Dim MyMonth
MyMonth = Me![chooseMonth]

'Dim MySql as String ' You have this in the form's declaration, right?
MySql = "" 'Clear the string variable

'Construct the MAIN SQL statement here
MySql = MySql & "SELECT qryGSTRecieved.* FROM qryGSTRecieved "

'Now test for and add the WHERE portion here
If Len(MyMonth) > 0 And InStr(1, MyMonth, "All") = 0 Then
MySql = MySql & "WHERE (((Format(qryGSTRecieved.PaidDate,"yyyymm"))=("
MySql = MySql & MyMonth
MySql = MySql & "))) "
End If

' add the ORDER BY here.
' -- I changed this from being conditional. You'll always want it sorted
this way, correct?
MySql = MySql & "ORDER BY "
MySql = MySql & qryGSTRecieved.PaidDate

'Finally, "close out" the SQL string using the semi-colon
MySql = MySql & "; "

'Check the resulting SQL string.
'If an error is displayed, you can copy the SQL string from the debug
window
'into a new query in order to help determine why it isn't working
'Debug.Print MySQL 'Uncomment this line and post the resulting SQL string
if this doesn't work.

'Setting the subform's Recordsource here automatically updates the display.
'No need to requery the subform.
Me.fsubGSTRecieved.Form.RecordSource = MySql

End Sub
*************************************************
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"DD" <da**********@bigpond.com.au> wrote in message
news:14**************************@posting.google.c om...
I have a mainform with a subform.
The main form has a dropdown box "chooseMonth", in the afterupdate event
i requery the subform so all records with the same date are viewed.
Now i only want to print the selected records of the selected month
Can any one advise on the below
Below is code i am trying to adapt (Thanks Don)
1.This first part is causing me problems so i have deleted it as it
strikes me that it is looking for a listbox and i have a form with a
subform please correct me if i am wrong as an error appears with the
VarItm.

---------------------------------------------------------------
Yes you are right in assuming that that was a ListBox control ... sorry, I
should have mentioned that.
--------------------------------------------------------------- 2.
The other part of the code is what i am working on, my problem is that
Me.choosemonth is giving me an error 3070 does not recognise 200403 as
a valid field .
Where do i get this number from? qryGSTRecieved
PaidMonth: Format([paidDate],"yyyymm ")
This is used in the dropdown box Me.ChooseMonth
-------------------------------------------------------------------------- ------ Deleted this

Dim ctl As Control
Dim varItm As Variant

Dim strSelected As String

Set ctl = Me.lstTransType
For Each varItm In ctl.ItemsSelected

If Len(strSelected) > 0 Then
strSelected = strSelected & ", " & chr$(39) &
ctl.ItemData(varItm) & chr$(39)
Else
strSelected = chr$(39) & ctl.ItemData(varItm) & chr$(39)
End If

Next varItm

'Debug.Print strSelected
-------------------------------------------------------------------------- ------ Using this

MySql = "" 'Clear the string variable

'Construct the MAIN SQL statement here
MySql = MySql & "SELECT qryGSTRecieved.* FROM qryGSTRecieved "

'Now test for and add the WHERE portion here
If Len(strSelected) > 0 And InStr(1, strSelected, "All") = 0 Then
MySql = MySql & "WHERE (((qryGSTRecieved.PaidDate)In("
MySql = MySql & strSelected
MySql = MySql & "))) "
End If

'Again, test for and add the ORDER BY here.
If Not IsNull(Me.chooseMonth) Then
MySql = MySql & "ORDER BY "
MySql = MySql & Me.chooseMonth
End If

'Finally, "close out" the SQL string using the semi-colon
MySql = MySql & "; "

'Check the resulting SQL string.
'If an error is displayed, you can copy the SQL string from the debug
window
'into a new query in order to help determine why it isn't working
'Debug.Print MySQL

'Setting the subform's Recordsource here automatically updates the
display.
'No need to requery the subform.
Me.fsubGSTRecieved.Form.RecordSource = MySql
Set ctl = Nothing

End Sub
============================

Nov 12 '05 #2
Hi Don
thanks for this.
Applogies to Dev Ashish

1. MySql = MySql & "WHERE
(((Format(qryGSTRecieved.PaidDate,"yyyymm"))=("
I have remove the " " around the yyyymm as it would not work otherwise.
2.Error says that qryGSTRecieved Varible not defined
MySql = MySql & qryGSTRecieved.PaidDate

3. My recordsource for DropDown box
SELECT DISTINCT qryGSTRecieved.PaidMonth,
Format([paidDate],"mmm yyyy ") AS Expr1 FROM qryGSTRecieved;

I am continuing to try to fix the problems
Thanks for all your help

DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3
Hi David,

Re #1:
Your formats for the data in the query MUST match the format of the data in
your combo-box.
You have Format(qryGSTRecieved.PaidDate,"yyyymm") in your SQL, and
Format([paidDate],"mmm yyyy ") in your combo box.
Your query will likely return ZERO records because it's trying to find
"200403" in a field that contains "Apr 2003"

Re #2
Ooops -- my fault... I forgot to include the quotation marks... should be:
MySql = MySql & "qryGSTRecieved.PaidDate"
Give those two things a whirl.
If it STILL doesn't work, please post the entire SQL string.

Regards,
Don
============================

"David Deacon" <da**********@bigpond.com.au> wrote in message
news:40*********************@news.frii.net...
Hi Don
thanks for this.
Applogies to Dev Ashish

1. MySql = MySql & "WHERE
(((Format(qryGSTRecieved.PaidDate,"yyyymm"))=("
I have remove the " " around the yyyymm as it would not work otherwise.
2.Error says that qryGSTRecieved Varible not defined
MySql = MySql & qryGSTRecieved.PaidDate

3. My recordsource for DropDown box
SELECT DISTINCT qryGSTRecieved.PaidMonth,
Format([paidDate],"mmm yyyy ") AS Expr1 FROM qryGSTRecieved;

I am continuing to try to fix the problems
Thanks for all your help

DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: thomas j | last post by:
Hi, I have a MySqlBase with a tabel named CARS and with 3 fields; CarsType, CarsModel, CarsInfo. Now I want to generate 2 dropdownboxes in PHP read from CARS. First I will "load" CarsType into...
0
by: Ian | last post by:
(Sorry if I have repeated this, it did not appear the first time) I have the following code on a button. The idea is that when this button is clicked it prints several reports automatically then...
1
by: DD | last post by:
I have a mainform with a subform. The main form as a dropdown box "chooseDate", in the afterupdate event i requery the subform so all records with the same date are viewed. Now i only want to...
1
by: DD | last post by:
I have a mainForm with a subForm On the main form i have a combo with March 04, April 04 etc, you select a date and the subform shows all records for the selected date. I can print a selected...
3
by: DD | last post by:
I have frmMain and fsub you choose a month from the ChooseMonth combo and all the records for that month are nopw visible in the fsub I know print with the following Where and only recieve the...
3
by: DD | last post by:
I have a mainform with a subform. > The main form has a dropdown box "chooseMonth", in the afterupdate event > i requery the subform so all records with the same date are viewed. > Now i only want...
1
by: CorporateCoder | last post by:
Hi, I am trying to bind the selected value of a databound dropdown box in a databound gridview control to the value being displayed in the template column the dropdown box has been added to. ...
7
by: itm | last post by:
I have a mail out to send to a group of owners with multiple accounts. I want to limit the number of accounts that print on the first page to 20. I want remaining accounts to print on a second...
3
by: Tyler2 | last post by:
Hi i am using access 2002 sp3, I am developing a training database for a dairy company and I need to be able to schedule up and coming courses. All the data is entered into a schedule form and I...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.