Setting The Record Set Of A Query
Aug 15, 2006
Can i edit a query to equal a SQL string just like i do for my reports? for my reports i say:
DoCmd.OpenReport "Check Filter", acViewDesign, , , acHidden
Reports![Check Filter].RecordSource = strRecordSource
DoCmd.Close acReport, "Check Filter", acSaveYes
i tried to say
DoCmd.OpenQuery "Query1", acViewDesign, acEdit
Queries![Query1].RecordSource = strRecordSource
DoCmd.Close acQuery, "Query1", acSaveYes
i get an error in the middle line...how do i do what i'm trying to do?
View Replies
ADVERTISEMENT
Feb 8, 2005
Hello,
I suspect this is probably rather easy, but I've not been able to figure it out:
I am using a combo to select a record in a form. This works.
The problem is: When I navigate through the records in the form, the combo box does not "update". I need to be able to change the record displayed in the combo box as the record in the form changes.
Any ideas?
Many thanks in advance for assistance.
View 8 Replies
View Related
Oct 28, 2014
How to set a new record view as default in form view???
View 1 Replies
View Related
Dec 21, 2005
G'day. I've been browsing the forum trying to find a solution to a problem, without much luck, and am hoping that someone might be kind enough to help me more directly.
I'm a biologist and a newbie to access: with the distinction of being completely clueless about VBA. Nonetheless, I have been tasked with creating a database for storing data obtained from biological surveys of juvenile salmon and harvest/spawner surveys of adult salmon.
The bones of the db are in place and functional. The problem I have is streamlining the data entry process to minimise keystrokes/mouse clicks.
I have a subform for entering fish records with attributes such as 'species_name', 'fork_length', 'count', 'presence of tags, etc. The idea is that fish can be entered as individuals (count=1) or groups (count>1) with attribute data at the appropriate level (eg, fork lengths only entered for individuals. We typically measure the fork lengths of the first 20 individuals of each species and then tally the remainder).
One problem I face is that fish often occur in schools, and it becomes quite tiresome to click and select the same species_name combo box value for each record when entering multiple individuals of fish of the same species.
What I would like to do is set the default value for that combo box (on the fish subform) to match the value entered for the previous record.
I suspect that you could use the after update trigger to execute some code to change the default value each time something is entered manually into the combo box. Unfortunately, I know nothing about how to write the appropriate code.
Can anyone offer me some guidance?
Thanks in anticipation.
Sincerely,
Craig Dolphin
View 11 Replies
View Related
Aug 21, 2015
I work for a charity in the Pacific. I am trying to develop a very basic patient electronic health record for a new hospital, to be used until we get a more comprehensive patient management system in place.
I have set up basic tables, forms, and a welcome screen.
The idea is that the doctor or nurse will start at the welcome screen. From there, they can either enter a new patient, or add a new patient encounter.
There are four types of patient encounters: assessment, treatment/surgery, refraction, and follow-up. I have made tables and forms for these encounters.
My issue is finding an easy way for the doctor or nurse to quickly and easily make sure that the encounter form they are filling relates to the appropriate patient. I already have a patient form, and patient ID field across the tables. But I would like to be able to add a 'search for patient' button on each of the encounter forms (or any other relatively easy method) to make sure everything lines up.
Database draft.zip
View 6 Replies
View Related
Mar 27, 2006
Hi,
I have a table I am trying to set up a query. I got into the query and set up the fields as follows: Name, Address, Landlord, Lease Date, 90 Day Reminder, 30 Day followup.
I want to show all these columns in my query. I need to set up formulas for the 90 Day Reminder and the 30 day followup from the Lease Date field.
The 90 Day Reminder is the date 90 days prior to the Lease Date, and the 30 day followup is 30 days after the lease date.
I am having trouble setting up the formulas. I did it in design mode, and I tried filling in a formula and when I tried going into the dababase mode, it asks for for a parameter. I am having difficulty understanding what to fill in for the parameter. I am not sure about the formulas either.
Can someone help me???
Thanks,
Marilyn
View 1 Replies
View Related
Oct 11, 2005
I have a table with 20,000 names in column 1.
Columns 2-8 are Yes/No questions.
I would like to set up a query to see how many people answered Yes for any combination of questions. I think a crosstab is needed, but I just can't for the life of me think how to do it.
Any ideas?
View 3 Replies
View Related
Dec 1, 2006
I have made a query and i want a tickbox to be ticked on the results.. (an escalated tickbox)
for some reason i cant make a macro with the following:
OpenQuery
SetValue
The query i'm using to detect if something needs escalating is quite complex, which is why i cant use an openform or similar?
Any help would be appreciated. James.
View 2 Replies
View Related
Mar 11, 2008
Hello, I am working on a database to analyze weekly purchase results. With the goal being to determine the response rates for each promotion source that was used.
I have a table that contains PromoName, PromoCode, PromoQuantity, PromoSource and PromoDropDate. In another table I have imported 8 weeks worth of results and use the fields PromoCode and DatePurchased. An additional field called TransactionValue is auto assigned to the number value of 1.
I was able to create a query that looked at all the records with the same PromoDropDate and PromoSource field and then summed the values in the TransactionValue.
I would like to be able to see the results by week for 10 weeks but am not sure how to proceed. For example if the PromoDropDate is 1/10/08 I could look at the results based on DatePurchased being between 1/10/08 and 1/16/08, and then for 9 week periods after that.
I welcome any and all suggestions on the best way to approach this. thanks.
View 5 Replies
View Related
Oct 5, 2004
I'm making a database with 50 fields for the shops of my company. Among these fields, the data of 20 fields must be submitted to us (head office). The data of the remaining 30 fields (like customer address) is just for generating receipts.
At the begining, I set the validation rule of that 20 fields to be "Is not null" in the Form. If the required data (20 fields) is not entered, messages will appear and the record cannot be saved. Then I use the Query function to extract that 20 fields. However, our staff said they might not be able to obtain the information of the 20 fields in the first time. The customers may send them the info later on. So, it not good for me to set any validation rule in the Form.
I would like to ask how I can ensure they submit the data of the 20 fields if I release all the restrictions in the Form? Could I set any validation in the Query so that error message will appear if not all the required fields are filled?
Thank you.
View 1 Replies
View Related
Nov 11, 2013
I am trying to access the same query/report from different forms. For example I have a StudentAttendanceForm where users select a StudentID and then open the report. The query criteria for ID# is [Forms]!
[StudentAttendanceForm]![StudentID].
Can I access this same report from an event on another form. I'm thinking that if I can some how assign the StudentID from the form I want to access the report from, to the criteria on the StudentAttendanceReport Query, this is possible.
I am new to code but I am thinking something like:
SET [StudentAttendanceQuery]![StudentID] TO [StudentEnrollmentForm]![StudentID]
Open StudentAttendance Report
The StudentEnrollmentForm is the other form I want to access the report from
You may need a crystal ball for this one. Let me know what other info you need.
In general I am finding I am copying and tweaking queries to meet specific user needs. For example some want to access a single student and other need "batch" reports.
View 1 Replies
View Related
Aug 23, 2005
I have set up a query to pull data from within a date range.
I have written an SQL Statement to amend the format of the date field:
Effective Date: IIf([Date_Effective_From]="00000000",Null,DateValue(Mid([Date_Effective_From],7,2) & "/" & Mid([Date_Effective_From],5,2) & "/" & Mid([Date_Effective_From],1,4)))
Within my criteria I have:
Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2]
(Benefits being the form). If I enter date range 01/01/2005 to 01/05/2005 I receive all data from 01 January to 01 May - but including different years i.e 2002, 2003, 2004 etc.
Can anyone suggest anything I may have missed?
Thanks
View 1 Replies
View Related
Mar 16, 2008
Hello Friends,
I have Query1 that should get one parameter , how to set this parameter in a code.
Below the code.
Private Sub Command0_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Query1")
MyRS.MoveFirst
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'
Do While Not MyRS.EOF
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailName]
With objOutlookMsg
.To = TheAddress
.Display
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
View 2 Replies
View Related
Jan 17, 2006
I have a query where one field is Date:Now() so that todays date is forced in. However, it is giving me the date and time. I only want the short date, how do I achieve this???
Thanks!
View 2 Replies
View Related
Jul 25, 2007
hi
i have a query that returns a dataset that looks like this:
Account Status
123 Paused
123 Paused
123 Not Paused
I want to evaluate these results and in a second query, for the account 123 return the value "Not Paused" if there is at least one status set to "Not Paused".
How can i do this?
Thanks in advance.
View 13 Replies
View Related
Dec 13, 2007
I have a query that has 4 fields that are text fields in an external database. I have text boxes set up on a form which contain either null or >0 (depending on an option button selected). I want to pass that >0 to one field in the query and null to the other three. I have the following as criteria in my query:
[Forms]![frmSelectPayment]![txt2ndPayment] (same one for each of the 4 fields except the form field name changes)
Below is the code that sets the text boxes that are sent to the query:
Select Case [FraSelectPayment] 'depending on which pmt being pulled
Case 1 '6 payment plan only
stDocName = "qryGetPlusARImport"
Select Case [FraChoose]
Case 1
Me.txt2ndPayment = ">0"
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 2
Me.txt2ndPayment = Null
Me.txt3rdPayment = ">0"
Me.txt5thPayment = Null
Me.txt6thPayment = Null
Case 3
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = ">0"
Me.txt6thPayment = Null
Case 4
Me.txt2ndPayment = Null
Me.txt3rdPayment = Null
Me.txt5thPayment = Null
Me.txt6thPayment = ">0"
Case Else
MsgBox "Please select a payment"
FraChoose.SetFocus
Exit Sub
End Select
DoCmd.OpenQuery stDocName, acNormal, acEdit
If I run the query alone I leave all fields blank and put >0 on the one I want to search by it works fine (695 records returned) but when I run it through code, nothing is returned. I really hope this makes sense to someone! Can anyone see what I am doing wrong?
Thanks
View 3 Replies
View Related
Oct 6, 2004
I know its probably a simple one but I am new to access, so...
I have a simple membership database and in it I keep a record of who I am expecting to attend on a particular date. This is achieved through having a seperate field for each meeting on a simple yes/no type.
I would like to be able to print a report from a form for a particular date showing everyone that would be attending. I have a list box that shows all the fields. What I cant do is set the date I want in the query from the report, can anyone help?
thanks
View 2 Replies
View Related
Jun 19, 2014
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable
SELECT QryTable01.productName
FROM QryTable01
GROUP BY QryTable01.productName
PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable]
t
Then the report row with the values:
Viable Not viable
14 12
displays as 1412
So how do I add the values in the rows???
View 1 Replies
View Related
Dec 9, 2014
I have a combo box field on a Student Details form that displays the Student's class number. For a new record, I am able to set the default value in the control's Default Value property with a literal in quotes (ie., ="2015-1"). But I want to set the default value from a query field. However, when I specify the query field in an expression ((=[Current Class]![Class Number]), "#Name?" appears in the field when initiating a new record on the form.
The control source for is the field in the Students table ([Students.[Class Number]).
The Row Source is a query of the Class table.
How can I set the control's default to the Class Number value in the Current Class query?
View 14 Replies
View Related
Oct 30, 2006
Looking to be pointed in the corect direction,
having trouble using a text box on a form to set the search criteria for a particular field within a query.
Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,
I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.
If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,
If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed???
The text box on the form also becomes locked, not allowing data to be entered.
I have tried to link the query direct to the text box.
also tried creating a table which has data entered via form then linking the query criteria to a field in the table.
Could some one point me in the right direction please
many thanks
Cheers
watson
View 1 Replies
View Related
Jun 21, 2007
Hi everyone,
Please would someone be able to help me?
I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format
'00000'.
Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.
Thank you in advance for your help.
Nats
View 5 Replies
View Related
Jul 6, 2007
Putting this in the field box in a query:
read or write: "r"
should create a field called read or write with a value of r in each record in the query, shouldn't it?
Why doesn't it, and how can I make it work?
View 2 Replies
View Related
Feb 26, 2007
I am trying to create a new Table using a MakeTable Query
using the following sql:
Code:SELECT qCPPlannedStopsOnTargetTotals.WeekNumber, qCPPlannedStopsOnTargetTotals.Line, qCPPlannedStopsOnTargetTotals.Description AS Above, qCPPlannedStopsOnTargetTotals.[%] INTO mkCPPlannedStopsAboveFROM qCPPlannedStopsOnTargetTotalsWHERE (((qCPPlannedStopsOnTargetTotals.Description)="Above"))ORDER BY qCPPlannedStopsOnTargetTotals.WeekNumber DESC;
However i want the new table to have a Primary Key, (Week Number) can i set this as the table is made?
Please help
Andy
View 1 Replies
View Related
Feb 4, 2014
I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.
In my query I have a name field where I put the criteria: Forms!Formname.Textboxname
By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.
Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.
View 7 Replies
View Related
Mar 18, 2014
I need some syntax in setting 2 criterias for a DLookup query.
I've attached a sample db with 2 tables: Main & Timesheet
I need a "combo" query showing (on the same line) all Qty for Transcodes N, 1 & 2 where the Staff number and TSNum is the same.
I'm sure about the logic but the syntax is letting me down.
I can pull in 1 of the criteria E.g.:
OT1: DLookUp("Qty","ExOT1","[Staff] =" & [Staff])
But can for the life of me not script the second criteria in.
So in my result of ExCombo I'm getting Staff 11 showing 4 under OT1 while I know the result does not apply for TSNum 29832 as indicated hence the need for the 2nd criteria.
Since I have to change some of the values to text inside the query it might be best to have a look at the attached db rather than just suggesting the correct syntax .
View 3 Replies
View Related
Jul 21, 2006
I have this criteria which should collect a date range (cboDate and cboDate2), it works well in collecting the date range if i put separate days (like 6/17/2006 and 7/18/2006, it'll collect the data matching those dates), but if i put the same day, say i want to get all the data for 6/17/2006. And cboDate and cboDate2 are both 6/17/2006. With this code, nothing comes up. Can you help me?
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or
[tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And
([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or
[tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
View 3 Replies
View Related
