Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 24th, 2004, 05:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert multiple records

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
  #2 (permalink)  
Old October 24th, 2004, 07:33 AM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
  #3 (permalink)  
Old October 25th, 2004, 02:29 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
  #4 (permalink)  
Old October 25th, 2004, 03:51 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
  #5 (permalink)  
Old October 26th, 2004, 06:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






  #6 (permalink)  
Old October 26th, 2004, 07:37 AM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
  #7 (permalink)  
Old October 27th, 2004, 05:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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










  #8 (permalink)  
Old October 27th, 2004, 07:35 AM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
  #9 (permalink)  
Old October 31st, 2004, 01:42 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


























  #10 (permalink)  
Old October 31st, 2004, 11:10 AM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"




Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert multiple records from form lorirobn Access 2 April 9th, 2007 03:08 PM
insert multiple records into a table from values Deepak Chauhan Oracle 3 May 12th, 2006 10:35 PM
insert multiple records mateenmohd Classic ASP Basics 19 October 18th, 2004 01:22 AM
Insert multiple records in a Inner Join Table rylemer Access VBA 0 July 19th, 2004 03:54 PM
Best way to insert multiple records koo9 ADO.NET 2 June 28th, 2003 08:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.