Combo Parameters in MS Access 2000???

[rickzeen]

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.

[ADezii]

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:
   

   Dim txtTDate As TextBox
   Dim cboEmp As ComboBox
    
   Set txtTDate = Me![txtTravelDate]
   Set cboEmp = Me![cboIndividual]
    
   If Not IsNull(cboEmp) Then
     If IsNull(txtTDate) Then
       MsgBox "You must enter a Travel Date", vbExclamation, "No Travel Date"
         txtTDate.SetFocus
     ElseIf Not IsDate(txtTDate) Then
       MsgBox "You have not entered a valid Travel Date", vbExclamation, "Invalid Travel Date"
         txtTDate = Null
         txtTDate.SetFocus
     Else
       'We have a valid Traveler and Travel Date, proceed and pass the information
       DoCmd.OpenReport "rptEmployees", acViewPreview, , , acWindowNormal, cboEmp & _
                        "," & txtTDate
     End If
   Else
     MsgBox "You must select the individual who is traveling", vbExclamation, "No Traveler"
       cboEmp.SetFocus
       cboEmp.Dropdown
   End If

2. In the Open() Event of the Report retrieve those Criteria, and also build a Record Source for the Report:
   

   Private Sub Report_Open(Cancel As Integer)
   Dim varInfo As Variant
   Dim lngEmpID As Long
   Dim dteTravelDate As Date
   Dim strSQL As String
    
   varInfo = Split(Me.OpenArgs, ",")
    
   lngEmpID = varInfo(0)
   dteTravelDate = varInfo(1)
    
   strSQL = "Select * From Employees Where [EmployeeID] = " & lngEmpID & _
            " And #" & dteTravelDate & "# Between [From] And [To];"
               Me.RecordSource = strSQL
   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:
   

   Private Sub Report_NoData(Cancel As Integer)
     MsgBox "There were no Records that matched the specified criteria", vbCritical, "No Records Found"
       Cancel = True
   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.

[rickzeen]

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

[ADezii]

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.

[rickzeen]

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

[rickzeen]

The 2 dates above should be in 2 text boxes.

[ADezii]

The 2 dates above should be in 2 text boxes.

It's probably in the Syntax, try something like:

"Between #" & Me![txtStartDate] & "# And #" & Me![txtEndDate] & "#"

[rickzeen]

It's probably in the Syntax, try something like:

"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

[ADezii]

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.

Dim strSQL As String
 
strSQL = "SELECT tblEmployee.Name, tblCourse.[Course StartDate], tblCourse.[Course EndDate], " & _
         "tblCourse.Title FROM tblEmployee INNER JOIN tblCourse ON " & _
         "tblEmployee.EmployeeID = tblCourse.EmployeeID Where tblCourse.[Course StartDate] > = " & _
         "#" & Me![txtStartDate] & "# And tblCourse.[Course EndDate] <= #" & Me![txtEndDate] & "#;"

[rickzeen]

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.

[ADezii]

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

[rickzeen]

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
%>

[ADezii]

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):

Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly=0
adLockReadOnly=1
adCmdTable=2

[rickzeen]

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