|
Subject:
|
Insert multiple records
|
|
Posted By:
|
mateenmohd
|
Post Date:
|
10/24/2004 5:47:13 AM
|
it is possible to active the combobox at the time of generate the
report ?
I use following query to generate the report.
SELECT [customer].[salesdate], [customer].[pname], [customer].[refco], [customer].[airline], [customer].[travfr], [customer].[travto], [customer].[outamt], [customer].[tcollect], [tcollect]-[outamt] AS Expr1
FROM customer
WHERE [customer].[airline]=[input_airline];
above query ask Airline name, user give Airline Name
it generate the report. there are many Airlines. I want the list of Airline display in the combobox. when it ask Airline ?
how can do ?
Please help
Mateen
|
|
Reply By:
|
JLovell
|
Reply Date:
|
10/24/2004 7:33:09 AM
|
Mateen-
You must use a form that has a combo box, and either open the report from the form with a WhereCondition filter on the OpenReport or change your query to reference the form. To use a parameter that references the form, change your query like this:
SELECT [customer].[salesdate], [customer].[pname], [customer].[refco], [customer].[airline], [customer].[travfr], [customer].[travto], [customer].[outamt], [customer].[tcollect], [tcollect]-[outamt] AS Expr1
FROM customer
WHERE [customer].[airline]=[Forms]![frmAirlineSelect]![cmbAirline]
.. where "frmAirlineSelect" is the name of the form and "cmbAirline" is the name of the combo box. Give the user a command button to click that hides the form by setting the Visible property to False and then opens the report. The form must remain open while the report runs. In the Close event of the report, close the parameter form.
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
10/25/2004 2:29:09 PM
|
You can also create a form with a combo box populated from the list of airlines, and then put a button on the form that opens the report and passes the parameter at the same time. No parameters need be required by the query.
This is the code for the button's OnClick event:
Dim stDocName As String
Dim stLinkCriteria As String
Dim stAirline As String
stDocName = "rptYourReportName"
stSerial = Forms!frmAirlineForm.cboAirline
stLinkCriteria = "[Airline]=" & "'" & stAirline & "'"
DoCmd.Close
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Using this method, you can close the form instead of hiding it.
I use this on an asset management database so user's can look up an item by its serial number. I am sure there are many more serial numbers than airlines!
Another method I use is to create a query that requests input like this:
"Like [Enter the First Few Letters of the Airline:] & "*"
Then I create a Continous Form so the user can look up all the airlines that start with those letters and they won't have to know how to spell the whole name of the airline.
Then next to each name on the form, I put a button that links from the Airline PK in the form, to the Airline PK in the report.
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
10/25/2004 3:51:18 PM
|
I forgot to mention that you have to make sure your combo box is bound to the same column as your airline name, in this case. So you may need to change the bound column in the combo box from 1 to 2, or the appropriate one.
mmcdonal
|
|
Reply By:
|
mateenmohd
|
Reply Date:
|
10/26/2004 6:36:13 AM
|
Hi,
sorry, I could not success to open to combobox at the time of run the report.
I was also try the first reply.
I use the following code on the event procedure on click
as you give.
Dim stDocName As String
Dim stLinkCriteria As String
Dim stAirline As String
stDocName = "Sales Report"
stSerial = Forms!Baniyas.airline
stLinkCriteria = "[Airline]=" & "'" & stAirline & "'"
DoCmd.Close
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
where form name is baniyas
combobox name is airline
when I click the button it run the report but no data display
and no combobox open why ?
blank page display.
it may by possible to send by e.mail an small example of report (small db) that
when run the report, it open combobox and after give
select the value it display report.
regards.
Mateen
mateenmartin@hotmail.com
|
|
Reply By:
|
JLovell
|
Reply Date:
|
10/26/2004 7:37:02 AM
|
Mateen-
The combo box must be on the form where you're running the code you posted. The code is attempting to build a filter for your report. The report should have no parameters in the query that is the Record Source.
Put a combo box on the report to list all airlines and call it cmbAirlines. Modify the code like this:
Dim stDocName As String
Dim stLinkCriteria As String
Dim stAirline As String
stDocName = "Sales Report"
stAirline = Me.cmbAirline
stLinkCriteria = "[Airline]=" & "'" & stAirline & "'"
DoCmd.Close
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
|
|
Reply By:
|
mateenmohd
|
Reply Date:
|
10/27/2004 5:56:53 AM
|
thanks for your help.
it is working, and display the report which is select from the combobox.
I have two more problems.
I have a columnar form, which many fields ie.
Passenger name, airline, ticket type, mode of payment......
1. when I want to search records. ie.
I want to find how many passenger travel the give airline ie British Air.
and click search button. it only display one record.
I want that if 10 passenger travel by british air, when I click
search button, it display 10 passenger records in Tabular form
if 20 passenger travel, it display 20 records. how ?
2. seconds when I search records. ie.
it should display records order by.
it only display one record, and next records something differnt.
it should display same name passenger records
how can difind the records that it return in order by in tabular form ?
regards.
Mateen
|
|
Reply By:
|
JLovell
|
Reply Date:
|
10/27/2004 7:35:16 AM
|
Mateen-
What is the code behind the Search button you are clicking? What is the Record Source of the form (the SQL of the query)? If you want the records sorted in a specific sequence, you must either apply a sort or use a query that asks for the records sorted the way you want them.
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
|
|
Reply By:
|
mateenmohd
|
Reply Date:
|
10/31/2004 1:42:45 AM
|
thanks for your response.
sorry, I could not reply, I was busy in work.
I want that when I click open form button it ask the contractno,strdate,enddate and then open the form (tabular) and retrieve the records from the following query.
SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate,assignments.cshift FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno where approvals.contractno=[contractno] and aenddate between [strdate ] and [enddate ] , assignments.aposit
data like this.
empno, aposit astrdate, aenddate,cshif, acontract
------------------------------------------------------
101 RE 1/1/2004 8/31/2004 II 2016
102 Secretary 1/1/2004 8/31/2004 II 229
103 RE 1/1/2004 8/31/2004 II 2010
...........
I want to change some data ie.
empno, aposit astrdate, aenddate,cshif, acontract
-----------------------------------------------------
101 RE 1/1/2004 9/31/2004 I 221
102 ARE 1/1/2004 9/31/2004 II 2016
103 RE 1/1/2004 9/31/2004 III 2002
...........
I have to insert the above data into the table.
when I click add records button (insert) it insert the records into the database table.
insert query like this.
insert into assignments2 (empno,aposit,astrdate,aenddate,cshift,acontract) values ("empno","aposit","astrdate","aenddate","cshift","acontract")
(1) How I can use select query in the button (open form button) on click event that it dispaly records in tabular form ?
(2) How I can use insert query in the button (add record) that it insert the records in to the table. ?
(ie insert the records not update the records, previous data should be same)
open form button on click envet code like this (builtin).
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "dbo_assignments6"
DoCmd.OpenForm stDocName, , , stLinkCriteria
add record button on click event code like this.
DoCmd.GoToRecord , , acNewRec
tabular form name is dbo_assignments6
I am using sql queries, Access tables are link with the sql server 2000.
advance thanks for your help.
Mateen
|
|
Reply By:
|
JLovell
|
Reply Date:
|
10/31/2004 10:10:56 AM
|
Mateen-
This is the first time you have mentioned SQL Server - that's important! Is this code in a desktop database (.mdb) or in a project file (.adp)? And which version of Access are you using?
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
|
|
Reply By:
|
mateenmohd
|
Reply Date:
|
11/1/2004 12:41:06 AM
|
thanks for response.
this is in a desktop database .mdb
Access 2000 9.0 version
Mateen
|
|
Reply By:
|
JLovell
|
Reply Date:
|
11/1/2004 9:07:05 AM
|
Mateen-
OK, then using linked tables means you can treat it as though it is an all-mdb setup.
The best way to do this is to remove the WHERE clause from your SELECT statement and provide a WhereCondition when you open the form bound to that query. The form that has the command button to open the form dbo_assignments6 should also have three controls - one each for ContractNo, StartDate, and EndDate. When the user clicks the command button, code in the Click event should examine the three controls and build a filter string - basically a WHERE clause but without the WHERE keyword. Something like this:
Dim strWhere As String
If IsNull(Me.ContractNo) Then
MsgBox "You must specify a Contract Number."
Exit Sub
Else
strWhere = "approvals.contractno = " & Me.ContractNo
End If
If IsNull(Me.StartDate) _
Or (Not IsDate(Me.StartDate)) Then
MsgBox "You must enter a start date."
Exit Sub
Else
strWhere = strWhere & _
" AND aenddate >= #" & Me.StartDate & "#"
End If
If IsNull(Me.EndDate) _
Or (Not IsDate(Me.EndDate)) Then
MsgBox "You must enter an end date."
Exit Sub
Else
If Me.StartDate > Me.EndDate Then
MsgBox "Start date cannot be greater than end date."
Exit Sub
End If
strWhere = strWhere & _
" AND aenddate <= #" & Me.EndDate & "#"
End If
DoCmd.OpenForm "dbo_assignments6", WhereCondition:=strWhere
I do not understand your second question. Why do you think you need to insert rows? Your sample INSERT SQL is attempting to insert the values "empno", "aposit", "astrdate", "aenddate", "cshift", and "acontract" into a single new row. Are you wanting to copy records from another contract or date range?
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
|
|
Reply By:
|
mateenmohd
|
Reply Date:
|
11/2/2004 1:01:25 AM
|
thanks for response.
I use the following query in select statement without where clause, form dbo_assignments6 bound following query.
SELECT dbo_employees.empno, dbo_employees.name, dbo_approvals.contractno, dbo_assignments.aposit, dbo_assignments.acontract, dbo_assignments.astrdate, dbo_assignments.aenddate,dbo_assignments.cshift FROM (dbo_employees INNER JOIN dbo_approvals ON dbo_employees.empno=dbo_approvals.empno) INNER JOIN dbo_assignments ON dbo_employees.empno=dbo_assignments.empno
form dbo_assignments6 working fine. it display all data.
in another form I create button open form. and on the click event procedure I use your coding.
but when I click the button it give error on first row ie. Me.contractno that
" compile error : Method or data member not found "
I defined contractno ie
Dim Contractno as String
but error is same.
Actually, I make program on employees records. our employees working different contracts. when contract finish,
management assign new contract to that employees. I prepare monthly report that one contract how many employees
are working, ie report for the month of October. 10/1/2004 to 10/31/2004 20 employees are working.
for next month ie same report will make, but only I have to change the dates etc. rest of the data will remain
same. I want that when I prepare report for the next month I retrieve records of previous month, and do some changing, ie dates
and again insert the records into the tables.
Yes I have to insert multiple rows. (not single row ) how can insert multiple row by the form ?
ie one contract many employees are working, multiple row retrieve from the tables.
and insert multiple rows into the tables. how ?
data retrieve from the table as posted above.
regards.
Mateen
|
|
Reply By:
|
JLovell
|
Reply Date:
|
11/2/2004 8:15:45 AM
|
Mateen-
In my previous reply, I said: "The form that has the command button to open the form dbo_assignments6 should also have three controls - one each for ContractNo, StartDate, and EndDate." Did you do that? Me.ContractNo in the code is an attempt to reference the ContractNo control mentioned above. You can give the control any name you like, but the code needs to reference that name. The same holds true for Me.StartDate and Me.EndDate.
As for your second problem, the sample data you posted earlier appears to contain a start date and end date. If an employee is assigned to a project for another month, it would seem to me you would want to simply update the end date rather than copy the row. If an employee is assigned to a new project how would you know which rows to copy? And what is the Primary Key of this table?
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
|
