Query Based On Current Record Selection
right, struggling here.
Basically I have continuous form listing all the standard letters sent out when I double click on a letter, I need to to run a query for me. The code I have put behind the button is:
If ([StandardLetterType] = "Homework") Then
DoCmd.OpenQuery ("qryHomeworkClearMailMerge")
DoCmd.OpenQuery ("MergeHomework"), , , , , acDialog, Me.[StandardLetterID]
DoCmd.OpenReport ("rptHomework"), acViewPreview
Else
'
end if
and in the query I am first clearing the mailmerge yes/no field out which works fine and then running the merge to put the yes back in the current record so the query looks like this
Its an update query with update to yes for mailmerge and under the standard letter ID i have the criteria [Me].[OpenArgs]
is this completely wrong tried the [Forms!][frmFullStudentReport].[standardletterID] but it kept popping the box up asking for the number.
On the form I have the standardletterID and its hidden but there.
Any help would be greatly appreciated
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Query Based On Current Record
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way. I have three tables; Contacts, Companies, Company_Addresses Contacts: ContactID (P) CompanyID OfficeName Companies: CompanyID (P) Company_Addresses: CompanyID OfficeName (P) What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field. I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated. Sorry if this is in the wrong forum. Thanks. James
View Replies !
View Related
Dynamically Update Field Of A Current Record Based On Previous Record
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1". So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record. I have a report that will use these results to count only the number of records that have a "WWW" in "field_2". Is this doable, maybe in a query somehow? I should add that whatever the solution, it needs to be compatible with Access 2000.
View Replies !
View Related
Switching Subforms Based On A Record Selection
Right slightly complicated (in my eyes) and I have not found anything in previous threads to help but here goes: I have a main form (FRM_SUMMARYREVIEW) that has an unbound subform field on it (subfrmmaster). When FRM_SUMMARYREVIEW is first opened subfrmmaster has its sourceobject set to a form called FRM_OPENSUMMARY. This subform lists all my open escalations, in a continuous forms format and has a field in it called "escalationid" and when I click this field "subfrmmaster" changes it's sourceobject to another form called FRM_MAINDATA and should display the data relevant to the escalationid I have just selected. In the past I used to click on "escalationid" and it opened up FRM_MAINDATA as a new form, using the following code: Dim Searchstr2 As String Searchstr2 = "[Escalation ID] = " & Forms![FRM_SUMMARYREVIEW]![FRM_OPENSUMMARY].Form![escalationid] DoCmd.OpenForm "FRM_MAINDATA", acNormal Forms!FRM_MAINDATA.Filter = Searchstr2 Forms!FRM_MAINDATA.FilterOn = True However how can I get the subfrmmaster sourceobject to change and display the relevant escalation detail ? My other option is to have two subforms on the main form, one with "FRM_OPENSUMMARY" the other with "FRM_MAINDATA", as "FRM_OPENSUMMARY" has a list of escalations displayed, in a continuous forms format could I set it so as I arrow down the list, the data in FRM_MAINDATA displays the highlighted escalation in "FRM_OPENSUMMARY". I hope this makes sense MattP
View Replies !
View Related
Find Record In Main Form Based On Selection In Subform
Can someone tell me how I migh find a record in a main form based a a selection in my subform? I have a Main form called frm_ProductionSchedule. It contains three subforms: frm_ProductionSchedule_subform frm_ProductionSchedule2_subform frm_ProductionSchedule3_subform I would like to be able to select a record within any of the subforms and have the main form display it. The main form is linked to a SQL server Table. The subform are based on queries. The unique PK field is a date field called record_date. I should add that the Link Child/Master fields are blank for all Subforms. Each Subform is linked to a query and displays data for a particular machine (1,2 or 3) AND is updated based on a combo selection of the Production week. The queries for one subform would look something like this: SELECT dbo_tbl_ProdSchedule.PartType, dbo_tbl_ProdSchedule.PartNumber, dbo_tbl_ProdSchedule.Work_order_no, dbo_tbl_ProdSchedule.FrameType, dbo_tbl_ProdSchedule.FrameNumber, dbo_tbl_ProdSchedule.Shift, dbo_tbl_ProdSchedule.Sch1_date, dbo_tbl_ProdSchedule.Sch1_spins, dbo_tbl_ProdSchedule.Sch1_sheets, dbo_tbl_ProdSchedule.Sch2_spins, dbo_tbl_ProdSchedule.Sch2_sheets, dbo_tbl_ProdSchedule.Sch3_spins, dbo_tbl_ProdSchedule.Sch3_sheets, dbo_tbl_ProdSchedule.Sch4_spins, dbo_tbl_ProdSchedule.Sch4_sheets, dbo_tbl_ProdSchedule.Sch5_spins, dbo_tbl_ProdSchedule.Sch5_sheets, dbo_tbl_ProdSchedule.Sch6_spins, dbo_tbl_ProdSchedule.Sch6_sheets, dbo_tbl_ProdSchedule.Sch7_spins, dbo_tbl_ProdSchedule.Sch7_sheets, dbo_tbl_ProdSchedule.Frames_due_date, dbo_tbl_ProdSchedule.Comments, dbo_tbl_ProdSchedule.Record_date FROM dbo_tbl_ProdSchedule WHERE (((dbo_tbl_ProdSchedule.Sch1_date)=[Forms]![frm_ProductionSchedule]![cmbSchDisDate]) AND ((dbo_tbl_ProdSchedule.SpinCoaterNo)=1)); AND WHERE ((dbo_tbl_ProdSchedule.SpinCoaterNo)=2)), etc. Respectively for each subform. Thanks in Advance!! John
View Replies !
View Related
Combo List Based On Current Record
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way. I have three tables; Contacts, Companies, Company_Addresses Contacts: ContactID (P) CompanyID OfficeName Companies: CompanyID (P) Company_Addresses: CompanyID OfficeName (P) What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field. I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated. Sorry if this is in the wrong forum. Thanks. James
View Replies !
View Related
Printing A Report Based On The Current (new) Record
Hi All, Having a problem with a report, I have a form that i use to enter a new record to my database, what I want to do is put a button at the bottom of the form to print the record (via a report) If I base this on a record that already exists, it works fine. However, If I have just entered the record the query the report runs off doesn't see the new record. To print that record I have to exit the completed record, then go back in to it and print. My aim is to remove this additional part of the process, I'm sure it's something really simple, but can anyone help? Many Thanks Paul
View Replies !
View Related
Filter A Listbox Based On Current Record
Hi I have a form which displays records of our products, on that form I have now put a list box which displays the ancillaries to the products. both the form and the list box run off queries. I have the same unique identifiers in both queries. I need to filter the listbox by the unique identifier every time I move record. At the moment the list box stays populated with all the ancillaries, how do I filter it programatically to list only the ancillaries with the same unique identifiers as in the current record? Thanks a million....
View Replies !
View Related
Print Single Record Based Upon Current Form
I need to Print a single record from a form but using a report layout. I have created the form and created a report. A button on the Form kciks off the print but I get all records rather than just the one shown on the Form. Whats a really simple way of just selecting the Current record. Where do I put an instruction saying print only this record:confused:
View Replies !
View Related
Conditional Format Based On Previous And Current Record
I'm having trouble with a tricky conditional format in a continuous subform. The criteria is based on field [GroupNo] when it changes (increases) which are always listed in numerical order. Basically all I want to is for the text box colour to alterante (eg blue & yellow)when GroupNo changes value So it would look something like this.... Group NoID (highlight colour) 1 (yellow) 1 (yellow) 1 (yellow) 2 (blue) 2 (blue) 2 (blue) 3 (yellow) 3 (yellow) etc etc btw, although values will always be in order they may not all be output, so it's not just case of odd, even, odd, even (that would be too easy!) I can retrieve the Previous Record's GroupNo value (via a function) in another text box and compare current GroupNoID with this in the Format Expressions and manage to change the colour, but it won't sustain this new colour for the subsequent same GroupNos, obviously as the values have changed. So how can I.... Change the colour when the value changes. Keep this new colour until it the value changes (increases) again. Many Thanks
View Replies !
View Related
Getting Data From One Table Based On Current Viewing Record
Hey all, I am working on converting someones Paradox Database to Access and making some modifications. I have a table with people in it and they are linked to a number of interests. But i am struggling to create a query so that on each page of the form for the person i can call the interests and display them on the form as a list... If anyone could point me in the right direction that woul dbe appreciated! Give me mysql and a bit of php anyday!! hehe I am using Access 2007 but i have used access before so i should be able to work my way through instructions for 2003. thanks RF
View Replies !
View Related
Run A Query/report Based On Selection From List
Dear all, is it possible to run a query (or ideally a report) based on a selection from a drop down list in a form? (in accsess 97). The reson being is i have a big list of of codes for different absence reasons & at present the user has to type in the correct code to display the data in a report based on the code they enter, however its not easy for users to remember which code to type in to run a report. Idealy what i would like is a drop down list with the absence codes & the absence desciption next to each code, then when the user selects a code from the list it displays the corresponding data in the report? Is this possible?, any help would be much appreciated, thanks.
View Replies !
View Related
Refreshing A Query Based On A Combo Box Selection
I have a form on which i make a selection. This selection then populates a query which in turn generates a report. The problem that i have is that when i go back into my form and make another selection the query, and therefore the report, holds the original data. My question is how can i refresh the selection and therefore produce different reports based on this selection without exiting the application? Many thanks for any help provided in advance.
View Replies !
View Related
Query Based On Current Month
I am looking at creating a query which would report whether a specific field is yes/no. However, I would like it to based on that days specific month. For example, I have 12 check box fields, one for each month, and I would like to create a report where it would look up whether or not the current month has a check box in it. Additionally, I would like to create another where the criteria would require me to put in a month to search for. For example, to search to see whether or not december has been checked. Tim
View Replies !
View Related
Refer To Previous Record In Current Record Query
In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e. Name Value Previous Record 1 1 Record 2 2 1 Record 3 7 2 Record 4 1 7 Is it possible to create some kind of simple expression to refer to data in another record? Cheers, Ben
View Replies !
View Related
How To Set Query On The Current Record
hi all, i create a form which have a button to append the current entry to another table but i only want to append the current entry tat is open. So how do u set the query to append one entry(the current open entry on the form) using the criteria. i notice the query only have sum, aver, +- ...etc.. tks.
View Replies !
View Related
Append Query Using The Current Record
I have a form with a subform in Datasheet view. I want the user to double click a record in that subform and a query to ammend it to another table. I want to trap the double click event of the subform but how do i run a query based on a current record in a subform?
View Replies !
View Related
Query Not Picking Up Current Record
Hello, Can anybody see what I'm doing wrong here. I have the following query SELECT Contacts.Cust_FK, Contacts.ContactNr, Contacts.FirstLastName FROM Contacts WHERE (Forms.ComLogDetail.Cust_FK=Contacts.Cust_FK) ORDER BY Contacts.ContactNr; The "Forms.ComLogDetail.Cust_FK" part should render the current record in the form ComLogDetail but its not. Any ideas?? I'm an amateur programmer so my apolgies if the solution is all too simple.
View Replies !
View Related
Refering To The Current Record In A Query
It is possible to refer in a SQL statement to the "current" or "actual" record?I want to base the querry in the present value of two related tables. I will try to show you what I have to do, maybe you can give an alternative solution.These are the tables:tblTeam (idTeam, TeamName, DayCreated)tblTeamPerson (idPerson, idTeam)tblPerson (idPerson, PersonName, MusicPreference)Every person have a music preference, and the teams have to be formed by people with similar music preference.So, one way to filter that is to use the following method: everytime I create a team, I choose a person, ok. That firts guy is the one that will give the team his "MusicalPreference". The next members with different MusicalPreferences will be excluded from the query.But in order to do that, I need to refer to the current team that have 1 member, and use the value of the field MusicPreference in that Person in particular.I hope you have understand what I have to do, and I hope you can help me with this.P.S.: I´m using a combo-box with a special query in a subform to select the team members. In the main form I create the team. So it would be great to be able to use that "idTeam" value in relation with the firts team member of that team.
View Replies !
View Related
Append Query To Current Record Only
This is my first time posting to a forum so don't hesitate to correct me if I am doing something wrong. I am trying to create autofill templates based on the worktype. How do I append data to the current record only! I have a table that contains all of the job information including a record autonumber. I have made a form using this table and it includes a subform for charge items from a separate table. My users are complaining about having to key in every line item and want the form to autofill the subform for charge items based on the worktype field. I have written an append query which is correctly extracting the worktype field and updating the charge items fields but it is doing so for every record with the same work type. I just want it to update the current record I am looking at.
View Replies !
View Related
Msg Box Based On List Box Selection
I have a form that contains a list box with 11 separate options. Within this list is the option named "None". My desire is to have a message box displayed when any option is selected other than "None". I have searched various threads, however I have been unsucessful nailing down my issue. Thanks in advance for the assistance.
View Replies !
View Related
New Form Based On Selection
Is there a way to take a form, select the fields that you want to display on a template(I created the template)? Then the fields that aren't selected grey/disable them? In other words. We perform several tests, ie. test 2006.01 and 2006.02 etc. Each test is based on 1 table. So instead of having to go in each time a new test is needed, can I create a form that has selectable fields(checkboxes), and once I say ok it opens the "new" template it will have the fields that need to be on that test then grey out the ones that aren't going to be used this time around? Sort of new to this....
View Replies !
View Related
Autofill Based On Selection - Please Help
I am learning Access fairly quick, but still a newbie and stuck on an autofill problem. I have read everything I could find and it seems there may be code involved, which I'm not sure about. The problem seems simple enough: I want to populate field "Rate" in the table "Services" based on the selection from a dropdown menu referencing ClientID in the Clients table. Specifically, my form uses a drop down menu to select ClientID for the Client table to associate with fields in the Project table and I have imbedded another form at the bottom which contains fields from the Services table including the Rate field I want autofilled based on the ClientID selection. Here are the tables and fields I'm working with... ...table... Client ...fields... ClientID (PK) Rate <and more> ...table... Project ...fields... ProjectID (PK) ClientID <and more> ...table... Services ...fields... ServicesID (PK) ProjectID Rate <and more> Please be easy on me if this is a simple problem. Thanks!!!
View Replies !
View Related
Making A Selection Based On A Month
Hi I have a subform on which I log the times spent on any particular project. I have another subform on which I want to select a month and use this to select all the instances on which work was done on any given project for a particular month. I would prefer to use a pop up calender to select the month (i.e. by slecting the last day of the month in question) Has anyone got any idea how I might do this - I am completely stumped. Thanks in advance Cheers Rob
View Replies !
View Related
Insert Relevant Value Based On Selection
I have a form with a combo box this combo box looks up values in my STC table. The STC table has two fields STC & Resolver and each STC has a unique associated Resolver. What i want to happen is after the user has selected the STC from the combo box on the form the associated Resolver be displayed in a textbox on the form. I am ussing the following code but nothing seems to be happening: Private Sub cboSTC_AfterUpdate() Dim ResolverSource As String ResolverSource = "SELECT tblSTC.[Resolver] " & _ "FROM tblSTC " & _ "WHERE tblSTC.[STC]='" & Me.cboSTC.Value & "';" Me.txtResolver.RowSource = AgentSource Me.txtResolver.Requery End Sub
View Replies !
View Related
Display Subform Based On Selection
A tricky one this. Searched for answer but cannot find. Acc 2003. I have a main menu with 2 subforms. In the on load event it sets the source object of the first subform, which displays a list of options (menu Style). When choosing an option from SubForm1, I want SubForm2 to display the form I have coded in. I currently have within subform 1 in the on click event the following. Form_MainMenu.subform1.sourceobject = "formtodisplayinsubform2" When running this from the main menu, nothing at all happens. When opening just the subform 1 on it's own and clicking the option, it merely opens the main menu form. Can anyone see what I am missing? Thanks in advance
View Replies !
View Related
|
TRACKER
