display the selected data on report through a from

hello everyone
i made a table named Registration the primery key of this table is Registration No and a form named registration to enter the data in that table now i want to dispaly that data through a from on a report or another form like i design a from having a textbox named Registration No and a command button named preview all i want is to enter the registration no in that textbox and on cliking the preview button all the data relevent to that registration no should dispaly sepradly on another form or report.
Note: data must display as i made my registration form
i m a new user to access database plez help me step by step that how, what and where i should write the command or code


Post your answer or comment

comments powered by Disqus
OK, what I have going on is this....The basic setup of the DB is as follows:

tbl_Products............... = Product List that includes Product_Code, Description, Pricing, etc.
tbl_Models...................= Contains a list of Car Models sorted by Make
tbl_Makes...................= Contains a list of Car Makes
tbl_Products_By_Model..= Creates many-to-many relationship between Models & Products, allowing a single product code to be linked to (or available for) multiple models...and allows models to have multiple products

I have attached 2 images...one is the relationships, the other is the form I'm using

On the left hand side of the form, you see where we enter the product info, description, pricing, product code, etc. and on the right side(sub form), we select which models this product is available for (which is a make/model with a specific date range)

Their are two combo boxes on the subform: one for make, one for model... The Make Combo pulls its values from tbl_Makes and the Models Combo pulls its value from a query of tbl_Models

Make Combo----------------------------------------------------------------
Control Source: Make
Row Source: SELECT tbl_Makes.ID, tbl_Makes.Make FROM tbl_Makes;
Row Source Type:Table/Query
Bound Column: 1
Column Count: 2
Column Widths: 0";

(this indicates I am pulling 2 values from tbl_Makes (the primary key and the Make Name)...it is bound to column 1 (the primary key) but does not display it and only displays the make name in the pulldown (this is working properly)

Model Combo-----------------------------------------------------------------
Control Source: Make
Row Source: SELECT qry_Models.ID, qry_Models.Model FROM qry_Models;
Row Source Type:Table/Query
Bound Column: 1
Column Count: 2
Column Widths: 0";

(this is pretty much the same as the Make Combo how it pulls 2 fields, is bound to primary key and only displays the model name in the pull down)
..however the source is a query that has criteria that only displays models that correspond to the make selected in the Make Combo

qry_Models has 3 columns (1 that links it to Makes to filter by criteria and the other 2 we pull into the form "ID" (which is hidden by the 0" column width) and the one we use to display the model name "Model"
This is the code I have under criteria of the first column of qry_Models.

Code: [Forms]![frm_Products_By_Model_Subform]![Make] And the following is the code I have on the Make Combo box that requeries the list of Models when the Make is changed

Code: Private Sub Make_AfterUpdate()Model.Requery End Sub ...When this requeries is when all data on all rows of the "Model" column changes

|Yr Start| |Yr Stop| | Make | | Model | (For instance if I have these selected )
| 1985 | | 1990 | | Chevrolet | | Camaro |
| 1990 | | 1992 | | Chevrolet | | Corvette | (FYI it works fine as long as I keep the same Make selected and do not update the Make column)
| 1991 | | 1995 | | Ford | Reply With Quote 01-04-2013, 01:19 PM #2 Casey Sanders Advanced Beginner Windows 7 64bit Access 2010 64bit Join Date Jan 2013 Posts 37 I was able to resolve part of this issue by switching from referencing a qry on the model combo box and using an SQL statement instead: Code: SELECT ID, Model FROM tbl_Models WHERE Make=[cbo_Make]; This allows me to select different Makes and their corresponding models properly....the problem I am having now is that once the correct model is selected, I go down to the next record, select the make and when I select the model, the model field on the previous record becomes blank... The data is still stored in the table and when I click on it again (or check the table) the selection is still there... The only thing I can think of is that I am pulling 2 columns in the Row Source (The autonumbered ID field and the field with the Model Name) and I have the column width set to 0"; which hides the first column (the autonumber)--which the combo box is bound to. If I unhide this and change "Limit to List" to "NO", then it shows ALL of the selected data on ALL rows, however it only shows the autonumber and not the model name... any suggestions??

Hi I am new to this forum i was wondering how to print selected data on a form? ie. not everything jus some control boxes and graphs is there a way
Cheers
rosari

Hi All,

I have seen this on the MS Access 2007 Templates(Students) that comes with the Application.

I have 4 tabs on the form. On the third tab I have a subform. I want the first column to be an Open/New button that when clicked, opens up a form that contains the same data on that row of the subform.

I' tried studying the template and thought will get it right on my DB, but im still failing.

Please asssist me on the step-by-step of accomplishing that.

Microsoft example attached!

Thanking you.
Phala.

Is there a simpler way to display the corresponding 'unbound' column entry of a combobox when the 'bound' column entry is selected? - or am I missing something?

I have a form with a 2-column combobox populated from its own table of about 16 records. It updates a 1-digit text field on this form with an alpha character (bound column -1). Works fine.

I created a listbox to display the other associated column (corresponding
'unbound' entry) to describe what the single alpha digit represents (much like the State Abbreviation-Name scenario).

I defined the row source* in the list box properties which works when I switch to its datasheet view or run it, but showed all 3 combobox selections, 1 for each of 3 records currently in the main table.

I put in a criteria entry to display only the contents relating to the selected alpha character the datasheet view displays one blank record (instead of the anticipated 1-line description, but on returning to the form the listbox displays correctly but doesn't change to the next corresponding description when next-record, >, is selected. The description remains the same fo all records (only 3 at present) and sometimes does't show at all until the form is closed and
reopened

I have also attached a Me.listbox.requery to the 'after update' property of the combobox (as well as the form's 'on load' property)

* SELECT NoPemp.NoPtype, Common.NoP
FROM NoPemp INNER JOIN Common ON NoPemp.NoPID = Common.NoP
WHERE (((Common.NoP)=[forms]![common f1].[CmbNoP]));

How can I populate the selected data from sub form to the drop down list? My sub form has 5 columns and only column 1 data of selected record will be populated in the drop down list. How can I do that and what VBA code I should use? Thank you.
Albert

I've searched for a while today and haven't found anything that made sense to me so i'm back with, what seems to be, a difficult request.

To keep things simple, just so I could figure out how to do this, I created a new database with 1 table. That table has 3 columns, the default ID column, a column titled "user" and one more titled "skill".

I put various names in the user column and 3 different skill levels in the skill column like what you see below:

UserA Novice
UserB Expert
UserC Intermediate
UserD Expert
UserE Novice

From here I am lost as to how to make a form take the data they are selecting to pass those values into a query for a report.

I can make a query ask for the values and get it to work that way but I want the form to determine the values, not the user typing in the information from the query popup. Also, I want them to be able to select more than one value because I have the form set up to use list boxes. If they want to see all users who are in the expert and novice categories then selecting both should display a report of all users and their respective skill levels.

The list box row sources show the correct data (because I used a query in the row source) and multi select works.

Getting that select data to pass through when clicking the "Submit" button is where i'm hitting a wall.

I've read other similar posts but there seems to be several steps left out for a beginner like myself to understand. Those steps, for a more advanced user, are probably just implied. I guess what i'm saying is if you don't mind...spell it out as much as possible. Thanks in advance for your help.

Using Access 2010

Hi Folks,

I have created a report based on a table. I am using the SUM funtion for a few fields. The problem is that the first page of the report is repeated for each account. If I have ten accounts, the same value is displayed ten times. Given the nature of my report, I do not need the report to be repeated as many times as there are accounts in my table -- all of the data would be exactly the same anyway. I have selected Hide Dulplicated to YES from the properties command for each field; but this does not work. Can someone please help me. Thank you very much.

Joe

I have a report that prompts the user to input starting and ending date parameters via an input box. How can I display these values on the report (showing on the report the beginning and ending dates entered)?

Here is my code:

	Code:
	Dim DateFilter As String
Dim Starting As Date
Dim Ending As Date
Select Case Me.optCompetitorDetailLevel
    Case 1  ' Detail
        stDocName = "Bid - Market Share Competitor Detail"
    Case 2  ' Summary
        stDocName = "Bid - Market Share Competitor Summary"
End Select
Starting = InputBox("Enter the beginning date", "Beginning Date", "1/1")
Ending = InputBox("Enter the ending date", "Ending Date", Date)
Select Case Me.optCompetitorDateType ' specify if report is to be filtered by proposal date or status date
    Case 1  ' Proposal Date
        DateFilter = "[ProposalDate] Between #" & Starting & "# AND #" & Ending & "#"
    Case 2  ' Status Date
        DateFilter = "[DateStatusUpdate] Between #" & Starting & "# AND #" & Ending & "#"
End Select
DoCmd.OpenReport stDocName, acViewPreview, DateFilter, , acIcon

Thanks for your help!
Scott

Hi All!!

New to AccessForums.net! I am a novice at Access. I have been using Excel forever, and I am pretty good at it, but finally have been running into projects that I cannot possibly do in Excel anymore I should have gone to Access a while ago. I am currently working on my second (very basic) database and running into a problem, so I am reaching out to everyone for any help you can provide.

Problem: I have a database of Employee Weekly Statistics based on 3 different criteria. I have a separate table for each of the 3 Categories. I would like to build a report that displays the last 4 weeks of each individual employee's 3 Categories.

I built a query that will display as follows, but I can't seem to figure out how to create a Printable Report for each employee that I can give them with the separate data.

Week Emp_Name Category1 Category2 Category3
1 Joe 80% 5 100
2 Joe 88% 6 102
3 Joe 89% 5 101
4 Joe 91% 5 100
1 Sam 75% 2 99

etc

Once the Data is displayed, can you put a Graph on the report, that can show you for Joe in Category1 he is Trending upwards? Maybe the graph is too much, I really just want to be able to start with displaying the data for each Employee, sort of like a Scorecard for them.

Any help will be greatly appreciated, or even pointing me in the direction of a helpful article on the web.

Thanks all!!

Hi all, hope you can help. I have three different queries established. One is called callcount(this is a sum of all calls) one is called salecount and the other is rejectcount.

If I wanted to display the total in one text box of these three queries added up how would I do that.

For example if callcount=1, salecount=2 and rejectcount=3 I want on my report total=6 (the sum of these three query counts)

Do I just adjust the properties on the control on the report to have a data source from all three queries. If so how do I do this.

I am sure the fix is simple but can't get there myself. As always any help is appreciated thanks.

mvpejp

I am a novice at access so please explain in the easiest way possible if possible.

I have a Table called "Citys"

I have a form with two combo boxes on it, Both with the same data "CityName"

I would like to be able to limit the data on my report so that only the citys between the two combo boxes appear on my report, example;

I select "Auckland" on the top combo box which is named "CityNameFrom" and select "Melbourne" on the second combobox which is called "CityNameTo", this would print only city's from Auckland to Melbourne and all others citys in between.

What would be my best way to do this as I need to apply this to most of my tables of information.

If anybody can help me it would be greatly appreciated but please include the code I will need to put in as I have a fairly basic database and knowledge of Access

Thank you in advance

I'm having a problem with sorting the data in one of my reports. I am using the following query as the record source in my report:

SELECT
Project.project_id, Project.project_name, Project.project_mgr, Project.prioritycode,[Project Info].project_id,[Project Info].employment, [Project Info].company_prod_service, Project_Updates.project_id,Project_Updates.note_da te, Project_Updates.update_notes
FROM (Project LEFT JOIN [Project Info] ON Project.project_id = [Project Info].project_id) LEFT JOIN Project_Updates ON Project.project_id = Project_Updates.project_id
ORDER BY Project.project_mgr, Project.project_name, Project_Updates.note_date DESC;


On my report, I have created two header in the Sorting and Grouping popup window:
FIELD/EXPRESSION --> project_mgr
SORTY BY --> ascending
GROUP HEADER -->Yes
GROUP FOOTER --> No
GROUP ON --> Each Value
GROUP INTERVAL --> 1
KEEP TOGETHER --> No
---------------------
FIELD/EXPRESSION --> project_name
SORTY BY --> ascending
GROUP HEADER -->Yes
GROUP FOOTER --> No
GROUP ON --> Each Value
GROUP INTERVAL --> 1
KEEP TOGETHER --> No

I have the project_mgr textbox in the project_mgr header and the project_name textbox int the project_name header.

In the Detail section, I have the note_date and update_notes textboxes.

Everything prints out in the correct order, except for the detail section. The note_date does not print out on the report in descending order. Instead the note_date, are ordered in a random order as far as I can tell.

When I run just the SQL query, it returns data in the correct order.
(BTW note_date is a Date/Time data type)

Can anyone help??

Thanks!

I have 9 fields on a Form that act as inputs to a Query. I then display the results of this Query in a Report. Is there any way I can display the 9 input fields as part of the Report Header?

Hi Guys, I am fairly new to Cyrstal Reports and have come across a small problem that escapes me how to display the needed data. My report is built to reflect missing invoices and has only 5 fields with the first being Table.Invoice. This is a string with 10 characters with the first 4 being leading zeros. I setup the parameter field to limit my searches to only 50 invoices. This part works great. Here is my problem. I need the report to reflect all 50 invoices in the display from the search input. If at any time a range is selected and there are missing invoices within the database the invoice number still needs to be displayed with no data in the other 4 fields. I have about given up on this. Can anyone shed some input on this?
Kind regards,
Mike

Hello everybody,

I have a search form, which works with the following code (wasn't written by me). If the search was successful, a new form pops up displaying the matching data.


	Code:
	 
Private Sub Befehl33_Click() 
'Suche starten 


    Dim rst As DAO.Recordset 
    Dim ctl As Control 
    Dim strCriteria As String 
    Dim strCriteria2 As String 
    Dim strCriteria3 As String 
    Dim strSQL As String 
    Dim strINList_Sitzung As String 
    Dim strINList_themen As String 
    
    strCriteria = "1 = 1" 
    For Each ctl In Me.Controls 
        If ctl.Tag  "" Then 
            If Nz(ctl.Value, "")  "" Then 
                Select Case ctl.Tag 
                    Case "Aufgabe", "Beauftragter" 
                        strCriteria = strCriteria & " AND [" & ctl.Tag & "] Like '*" & ctl.Value & "*'" 
                    Case "Datum_1" 
                        If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] >= " & Format(ctl.Value,
"#yyyy-mm-dd#") 
                    Case "Datum_2" 
                        If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum]

I have been working on a form which is based on a query which joins a few tables. It has been working as expected including having the ability to modify the underlying data using the form.

I have made a change to the query by joining another query to it which allows the addition of a single new calculated field. The new query works correctly and displays the correct data, however I am now unable to modify the underlying data as I was before.

I have checked that it is not a fault of the form as I can open up the query directly and cannot modify the underlying data, however if I remove the join to the second query i am able to modify the data again.

A very abridged version of my query follows:

SELECT tblMembers.M_Code,
tblMembers.M_Surname,
tblEquipment.STRAP,
tblEquipment.OTHER,
qryLOA.LOA
etc. etc.
FROM (((tblMembers
LEFT JOIN tblMember_Cats ON tblMembers.M_Member_Type = tblMember_Cats.Ca_ID)
LEFT JOIN tblEquipment ON tblMembers.M_Code = tblEquipment.MEMBERSHIP_CODE
LEFT JOIN qryLOA ON tblMembers.M_Code = qryLOA.M_Code)
WHERE (((tblMembers.M_Date_Left) Is Null))

Can anyone explain why joining to the second query prevents my ability to modify the data in the query? I can understand that the calculated data in the subquery could not be modified, but why not the other fields?
I would really appreciate some assistance on this one.

I suspect including the second query as a subquery within the first query may help, however it is complex with groupings and sums and I am unsure how I might do it - or even if it is the problem.

Hi,

Need an expert help on small report problem. I have a form where I can choose to view/print report of a requestor detail by status for a range of date ie: Approve, Rejected and etc. My problem is how do I set the report to only view/print the selected data. ( I use combo box for the list of request status

Hello all,

I'm fairly new to all this access stuff, so your help will be appreciated.

I have created a report (reportA) using the wizard based on a query(queryA).

When I run the query from the QBE window, a date parameter requests a date to be entered, based on a date form (formdate). This then displays the requested data in datasheet view.

When I run reportA from the report window, again the date is requested, before the data is displayed in the report.

I have then inserted the reportA in a cutomized menu, but when reportA is selected from the menu, a blank report is displayed, and no date parameters are requested.

How do I set the report up in the customized menu, so when a user selects reportA from the menu, they are prompted for the date via formdate, and then when the date is selected, reportA displays the selected data for the date range.

I hope this make sense,

regards

C

I am an Access newbie and have two related questions: How to I craft a form which will filter data based on user input for output on a report? I have not been successful trying the CRITERIA setting on a query. I can see in the Northwind example a SALES REPORT DIALOG that does exactly what I want to do, but I can only open this form in the FORM view, not the DESIGN view. If I could, I would be able to deconstruct what it's doing. Your help is much appreciated

I've been using Access for a little while, but I still consider myself a newbie when it comes to all the things that can be done through it.

This is a two-fold question, the majority dealing with Reports

I've got a database with a Hyperlink base, and I'm trying to set links up through the form without having to type in the full address every time.

Also, I'm trying to set up a report that takes the link so it won't actually display the full link, but list text supplied from a different field.

If more information is needed, please let me know.

Hi All,
Well this is foxing me and its probably obvious but missing it...what i have is a query for data source, a form with a sub form on it that is in dataview, in the subform data i have a yes/no field.
what i want to do but struggling with is this..the main form has only a cmd button, when clicked i want to pass data of only the records in the subform field checkbox thats ticked to the report, so if 2 of the records have the checkbox ticked only those 2 show in the report.
code i have is:

Dim stDocName As String
Dim strFilter As String
strFilter = "Me.frmCertificatesub.[Printcert]= '" & Me.[Printcert] & "'"
stDocName = "rptCertificates"
DoCmd.OpenReport stDocName, acPreview, strFilter

easy or should be but just cant get it working! it either shows all of the records if i use the above, or none if i use:
DoCmd.OpenReport stDocName, acPreview, ,strFilter

I have also tried splitting the StrFilter tna and all possible workarounds i know of but still get the same or errors, any help you guru's can offer appriciated

Hi dear all, I have a query like below
Cnt Type Time
1 TypeA 20/01
1 TypeB 23/01
4 TypeE 23/01
3 TypeC 14/02
24 TypeA 25/02
564 TypeD 12/03
116 TypeE 24/03
0 TypeB 15/04
27 TypeD 25/04

And I have a multiple listbox on my form to select type

If the selection from the listbox is TypeB and TypeE
I want my report looks like
Time TypeB TypeE
23/01 1 4
24/03 0 116
15/04 0 0

I already made the multiple listbox pass to report VBA code, but my problem is only one column field shows on the top, like if the selection is TypeB and TypeE, there only TypeB on the column field and won't give me all the select type on the same page
Hope anyone can help me out, thanks!

Hello all,

I'm fairly new to all this access stuff, so your help will be appreciated.

I have created a report (reportA) using the wizard based on a query(queryA).

When I run the query from the QBE window, a date parameter requests a date to be entered, based on a date form (formdate). This then displays the requested data in datasheet view.

When I run reportA from the report window, again the date is requested, before the data is displayed in the report.

I have then inserted the reportA in a cutomized menu, but when reportA is selected from the menu, a blank report is displayed, and no date parameters are requested.

How do I set the report up in the customized menu, so when a user selects reportA from the menu, they are prompted for the date via formdate, and then when the date is selected, reportA displays the selected data for the date range.

I hope this make sense,

regards

C

Hi all

I am trying to display the selection made by the user in the list box. Its a Multi Selection List Box. I am trying to display to the user that he/she had selected this, this etc, depending on his/her selection in the list. The following code displays the selection individually. Can you think about displaying all the selection in a single msgbox?

Private Sub CommandButton1_Click()
Dim intloopindex
For intloopindex = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(intloopindex) Then
MsgBox " U have selected " & ListBox1.List(intloopindex)
End If
Next intloopindex

End Sub


I was thinking..... Can we include any loop inside the Message Box? Just a thought.

Thanks for your time.


Not finding an answer? Try a Google search.