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

Combo Parameters in MS Access 2000???

P: 8
Hi,

I am new to MS Access 2000, and i have designed one simple data base to keep record of people travelling every month

I can get the Report as a whole people as wizard report. but have tough time to get its own individidual report writing VB coding.. I want to select the individual from combo box and if possible date travel between can also be passed.


How do we pass the parimeter to get the own individual reports? Do any one have idea to write the MACRO or VB coding to get its own??

I would be very grateful for the simple answer.
Aug 27 '08 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,628
There are several ways to accomplish this, I'll give you my approach as a starter, and you can take it from there.
  1. Enter your Criteria for the Report on a Form, and validate the entries. Pass the Criteria for the Report in the OpenArgs Argument as a ',' Delimited String:
    Expand|Select|Wrap|Line Numbers
    1. Dim txtTDate As TextBox
    2. Dim cboEmp As ComboBox
    3.  
    4. Set txtTDate = Me![txtTravelDate]
    5. Set cboEmp = Me![cboIndividual]
    6.  
    7. If Not IsNull(cboEmp) Then
    8.   If IsNull(txtTDate) Then
    9.     MsgBox "You must enter a Travel Date", vbExclamation, "No Travel Date"
    10.       txtTDate.SetFocus
    11.   ElseIf Not IsDate(txtTDate) Then
    12.     MsgBox "You have not entered a valid Travel Date", vbExclamation, "Invalid Travel Date"
    13.       txtTDate = Null
    14.       txtTDate.SetFocus
    15.   Else
    16.     'We have a valid Traveler and Travel Date, proceed and pass the information
    17.     DoCmd.OpenReport "rptEmployees", acViewPreview, , , acWindowNormal, cboEmp & _
    18.                      "," & txtTDate
    19.   End If
    20. Else
    21.   MsgBox "You must select the individual who is traveling", vbExclamation, "No Traveler"
    22.     cboEmp.SetFocus
    23.     cboEmp.Dropdown
    24. End If
  2. In the Open() Event of the Report retrieve those Criteria, and also build a Record Source for the Report:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2. Dim varInfo As Variant
    3. Dim lngEmpID As Long
    4. Dim dteTravelDate As Date
    5. Dim strSQL As String
    6.  
    7. varInfo = Split(Me.OpenArgs, ",")
    8.  
    9. lngEmpID = varInfo(0)
    10. dteTravelDate = varInfo(1)
    11.  
    12. strSQL = "Select * From Employees Where [EmployeeID] = " & lngEmpID & _
    13.          " And #" & dteTravelDate & "# Between [From] And [To];"
    14.             Me.RecordSource = strSQL
    15. End Sub
  3. Check and see if there are no Records that meet the Criteria, since you do not want to Open an Empty Report:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_NoData(Cancel As Integer)
    2.   MsgBox "There were no Records that matched the specified criteria", vbCritical, "No Records Found"
    3.     Cancel = True
    4. End Sub
  4. The previously listed code will allow a User to select an Employee (cboIndividual) and enter a Date (txtTravelDate). If will then see if the Employee is on Travel that Date by checking to see if it falls between a specified Range ([From] ==> [To]). The code is generic enough, make your own substitutions, and if you have any questions, feel free to ask.
Aug 27 '08 #2

P: 8
HI Adeezi,

Thanks for your great help, the code has helped me little in giving some information and to generate report. But still i couldnot solve it though it would be trouble for you i would be helpful if u can just help me once again with those questions.

1. I did with all the codings in forms: but when i genereate report all the reports comes but not individual reports......so i did wrote the code in "Private Sub Report_Open(Cancel As Integer)"

as mentiuon by you. But this time this is error it comes. Type mismatch in "ID = varInfo(0)""


WHat couldbe the problem.


I want to explain you my table structure:

Table1:tblEmployee: with EmployeeID,Name, etc
table 2: tblCourse: with Course ID, EMployee id from tblemployee, Course Startdate, End date course, etc etc.


What i want is to to generate the individual report and another report which the people are travelled between any dates.


thanks for your kind help please
Aug 28 '08 #3

ADezii
Expert 5K+
P: 8,628
HI Adeezi,

Thanks for your great help, the code has helped me little in giving some information and to generate report. But still i couldnot solve it though it would be trouble for you i would be helpful if u can just help me once again with those questions.

1. I did with all the codings in forms: but when i genereate report all the reports comes but not individual reports......so i did wrote the code in "Private Sub Report_Open(Cancel As Integer)"

as mentiuon by you. But this time this is error it comes. Type mismatch in "ID = varInfo(0)""


WHat couldbe the problem.


I want to explain you my table structure:

Table1:tblEmployee: with EmployeeID,Name, etc
table 2: tblCourse: with Course ID, EMployee id from tblemployee, Course Startdate, End date course, etc etc.


What i want is to to generate the individual report and another report which the people are travelled between any dates.


thanks for your kind help please
Let's take one problem at a time.
  1. Error ==> Type mismatch in "ID = varInfo(0)""
  2. Is the EmployeeID that the Variable ID is referencing a String, Integer, Long etc., namely, what is the Data Type of [EmployeeID] in tblEmployee?
    What i want is to to generate the individual report
  3. What exactly do you wish Report1 to show? Explain logically, then list all Fields to be contained within the Report.
Aug 28 '08 #4

P: 8
What ever may be, i am able to generate the individual report now after i did it with Macro. but not tat perfect.

WHat i want to display in report is:


i want the monthly report from course table with employee detail from Employeetables.

whats the parameters to pass to generate for example:fromdate:01/08/2008 = todate:30/08/08.


thanks for your help tooo
Aug 28 '08 #5

P: 8
The 2 dates above should be in 2 text boxes.
Aug 28 '08 #6

ADezii
Expert 5K+
P: 8,628
The 2 dates above should be in 2 text boxes.
It's probably in the Syntax, try something like:
Expand|Select|Wrap|Line Numbers
  1. "Between #" & Me![txtStartDate] & "# And #" & Me![txtEndDate] & "#"
Aug 28 '08 #7

P: 8
It's probably in the Syntax, try something like:
Expand|Select|Wrap|Line Numbers
  1. "Between #" & Me![txtStartDate] & "# And #" & Me![txtEndDate] & "#"

Adizeee, you are great programmmer compare to us. i too had some idea before but now due to limted time i couldnot now i have started doing it and after geting your help i could start a bit and atleast gettting something done from my side. well once more i would like to get ur help in this as said earlier.


1. Now i want to generate monthy report i mean from tbl course: we have to concentrate on 2 text boxes containing 2 dates any date: so if those dates enter is between and if there is dates in tbl course: field start date and End date

then have to generate the report.

so whats the full code: Do i need to write sql to get date from tbl:

though simple i m having touhg time.

so please help me

thanks
Sep 1 '08 #8

ADezii
Expert 5K+
P: 8,628
Adizeee, you are great programmmer compare to us. i too had some idea before but now due to limted time i couldnot now i have started doing it and after geting your help i could start a bit and atleast gettting something done from my side. well once more i would like to get ur help in this as said earlier.


1. Now i want to generate monthy report i mean from tbl course: we have to concentrate on 2 text boxes containing 2 dates any date: so if those dates enter is between and if there is dates in tbl course: field start date and End date

then have to generate the report.

so whats the full code: Do i need to write sql to get date from tbl:

though simple i m having touhg time.

so please help me

thanks
Assuming a 1 To MANY Relationship between tblEmployee(1) and tblCourse(MANY) on [EmployeeID], the SQL would look something like the following to list all Employees taking Courses within the Range specified by your Text Boxes. I must, however, warn you that SQL is not my strong point, and you will probably be better off with Replys from other Experts/Moderators, so let's stand by and see what happens.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "SELECT tblEmployee.Name, tblCourse.[Course StartDate], tblCourse.[Course EndDate], " & _
  4.          "tblCourse.Title FROM tblEmployee INNER JOIN tblCourse ON " & _
  5.          "tblEmployee.EmployeeID = tblCourse.EmployeeID Where tblCourse.[Course StartDate] > = " & _
  6.          "#" & Me![txtStartDate] & "# And tblCourse.[Course EndDate] <= #" & Me![txtEndDate] & "#;"
Sep 1 '08 #9

P: 8
Adizee,

Thanks for your great help, I have done with the reports and forms too. I have done as per the guidance of you and ur codings. i had done with great with your help. SOme i have done using macro too. thank u once more.

well now lastly i need to make this Database as Exe file. But how can i go about. I want to create EXE file and when run... the file should open one main form and navigate all other forms from that main form.I had some idea in converting that from access 2003. But not in 2007.

I created ACCDE file but when i open that file, it shows as same file no change. How do i make that file to open the MAIN form only and then navigate from it.


Thanks if u can help in that too.
Sep 2 '08 #10

ADezii
Expert 5K+
P: 8,628
Adizee,

Thanks for your great help, I have done with the reports and forms too. I have done as per the guidance of you and ur codings. i had done with great with your help. SOme i have done using macro too. thank u once more.

well now lastly i need to make this Database as Exe file. But how can i go about. I want to create EXE file and when run... the file should open one main form and navigate all other forms from that main form.I had some idea in converting that from access 2003. But not in 2007.

I created ACCDE file but when i open that file, it shows as same file no change. How do i make that file to open the MAIN form only and then navigate from it.


Thanks if u can help in that too.
  1. Tools
  2. Startup
  3. Display Form/Page ==> enter your Start Up Form Name
Sep 2 '08 #11

P: 8
Adizee,
It has been great help from your side on developing the simple data base, well again i have developed one aspfile. which would retrive the datas in asp form. but when i retrive. the datas are all shown in page below .for eg. if submit the news now. that would just get at the end of page. so here is the retrival asp.code. how it is possible to make that the recent post are shown at the first line. not last.

Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly=0
adLockReadOnly=1
adCmdTable=2
Dim objConn, objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
Dim strDatabaseType
strDatabaseType="Access"
If strDatabaseType="Access" Then
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=C:\Inetpub\wwwroot\Websites\Internal\Data.m db;" &_
"Persist Security Info=False"
End If
objRs.Open "datatable", objConn,adOpenForwardOnly,adLockReadOnly,adCmdTabl e

While Not objRs.eof

Response.write "<style=Arial narrow;Font size=7;color=#000066>"
Response.Write "<b><center>"
Response.Write objRs("topic")
Response.Write "</b></center>"
Response.write "</font>"
Response.write "<font color=#550000 face=verdana size=2>"

Response.Write objRs("details")
Response.write "</font>"

Response.Write objRs("submitted")&"<BR>"
Response.write "<font color= #CC0033>"
Response.write objRs("division")&"<BR>"
Response.write "</font>"

response.write"<b>"
response.write "Date posted "
response.write"</b>"
Response.write objRs("date")

Response.Write "<HR height=1 color=white>"

objRs.Movenext
Wend
objRs.Close
objConn.Close
Set objRs=Nothing
Set objConn=Nothing
%>
Sep 10 '08 #12

ADezii
Expert 5K+
P: 8,628
Adizee,
It has been great help from your side on developing the simple data base, well again i have developed one aspfile. which would retrive the datas in asp form. but when i retrive. the datas are all shown in page below .for eg. if submit the news now. that would just get at the end of page. so here is the retrival asp.code. how it is possible to make that the recent post are shown at the first line. not last.

Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly=0
adLockReadOnly=1
adCmdTable=2
Dim objConn, objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
Dim strDatabaseType
strDatabaseType="Access"
If strDatabaseType="Access" Then
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=C:\Inetpub\wwwroot\Websites\Internal\Data.m db;" &_
"Persist Security Info=False"
End If
objRs.Open "datatable", objConn,adOpenForwardOnly,adLockReadOnly,adCmdTabl e

While Not objRs.eof

Response.write "<style=Arial narrow;Font size=7;color=#000066>"
Response.Write "<b><center>"
Response.Write objRs("topic")
Response.Write "</b></center>"
Response.write "</font>"
Response.write "<font color=#550000 face=verdana size=2>"

Response.Write objRs("details")
Response.write "</font>"

Response.Write objRs("submitted")&"<BR>"
Response.write "<font color= #CC0033>"
Response.write objRs("division")&"<BR>"
Response.write "</font>"

response.write"<b>"
response.write "Date posted "
response.write"</b>"
Response.write objRs("date")

Response.Write "<HR height=1 color=white>"

objRs.Movenext
Wend
objRs.Close
objConn.Close
Set objRs=Nothing
Set objConn=Nothing
%>
Sorry, but you will have to rephrase you question, I really do not understand its meaning. The following code is not necessary, since they are all Intrinsic Constants (built into Access):
Expand|Select|Wrap|Line Numbers
  1. Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
  2. adOpenForwardOnly=0
  3. adLockReadOnly=1
  4. adCmdTable=2
Sep 10 '08 #13

P: 8
thanks

The question is: i have one form design in asp file which is to be submitted and the information are automically written to acccess database. there is no problem getting the information written to access database. And another is i have one asp file which i coded above is to retrive the information from access and then it gets displays in web form. like we acess the guestbook n all.

while it displays.......the recent submitted information are not getting to top page while it gets to down page. so the above code in asp is one which it gets displays.....whats the change in code to get displayed as newly submitted information to top page
Sep 11 '08 #14

Post your reply

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