MS ACCESS :: Query Based On Current Record

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 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

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

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

Compare Current To Previous Record In Query
I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?

View Replies ! View Related

Discrepant Record Numbers Between Query And Table It Is Based On.
I have a temporary table with all text fields for importing data to a normalized table. In order to import I need to prep the data by pulling the numeric values of "lookup" type fields (nb. not actually using MS Access lookups)

My prep query is producing 9 more records than there is rows in the import table, even though every join is a LEFT join, which should just pull all import rows and match up lookup values where they are available.

eg. of import table (all fields are text)

Location;
Group;
Name;
Wfm type;
Account Number;
WFM Status;
Date Created;
Date Held To;
processdate

Beachville Operating Centre;
Service Orders;
DAVID JONES;
;
Complete Engineering Investigation;
Completed;
4/20/2007;
;
4/27/2007

here is my prep query (the iif is to replace userid's that can't be found with UNKNOWN)

SELECT lookup_location.val AS F1location, lookup_group.val AS F2group, IIf([lookup_user.val] Is Not Null,[lookup_user.val],"UNKNOWN") AS F3user, wfm.wfmcode AS F4wfmcode, CURPW020_import.[Account Number] AS F5account, lookup_status.val AS F6status, CURPW020_import.[Date Created] AS F7datecreated, CURPW020_import.[Date Held To] AS F8dateheldto, CURPW020_import.processdate AS F9processdate
FROM ((((CURPW020_import LEFT JOIN lookup_location ON CURPW020_import.Location = lookup_location.txt) LEFT JOIN lookup_group ON CURPW020_import.Group = lookup_group.txt) LEFT JOIN lookup_user ON CURPW020_import.Name = lookup_user.txt) LEFT JOIN wfm ON CURPW020_import.[Wfm type] = wfm.wfmname) LEFT JOIN lookup_status ON CURPW020_import.[WFM Status] = lookup_status.txt;

any assistance would be much appreciated. any other info needed let me know.

View Replies ! View Related

Excluding Multiple Records From Query Based On Value In ONE Record
Table1 gives Case Nu.

Table2 gives Case Nu. and EventType.

Table3 gives EventType and Cleared (yes/no field)

I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.

Any ideas? I know there must be an obvious solution, but my mind is stuck right now!

View Replies ! View Related

Running A Query Based On A Select Record In The Form
Hi,

I have a query that I would like to run from a form. I have created the button and the query, but want to add in a clause that will use the user selected record / field as the criteria for the query when its run.

Got any examples of code I could have a play with

I am struggling here so need any help I can get!!

Cheers
Paul

View Replies ! View Related

Simple, But So Difficult!! Updating Record In DAP Form Based On Read-only Query
Hi all, I'm so tired of looking... please help me!!

I have an Access Data Access Page based on a query which is not updatable.

The query is based on a many-to-many table (e.g. OfficeProducts with foreign keys for OfficeID and ProductID and an Amount field). The query is not updatable as I have included outer joins to the Office and Product tables to get all their records back even if there is no entry in OfficeProducts for that office or product the record is still shown with an Amount of null.

(I'm not sure if I've done this the best way, but need to be able to list all the possible Products for each Office whether they use them or not.)

I want the user to be able to update the Amount field, so e.g. if it is currently set to 5 to be able to change it to 4, or if it is currently null (as there is no record so far for that OfficeProduct) then to insert a record into OfficeProducts with the OfficeID, ProductID and Amount value entered.

I thought this would be straightforward by creating a new textbox in the same section as the current Amount field, calling it NewAmount and letting the user update this, fire an event trigger to either update or insert into the table, and refresh the query and record on the DAP so that Amount shows the newly updated/inserted value.

I have been searching for ages... cannot locate either the best event or events to use for this nor the code to enter!! Please please help!!!!!:confused:

Please tell me if there is another forum better suited to my query. Can't find the DAP or Vbscript one.

Ta muchly.

View Replies ! View Related

Report/letter Based On Info On Current Form
I have a booking form comprising of a main form which holds the person details, and a sub form which holds the details

of each course they book. I want to be able to produce a report/ letter from the current form (ie once a person has made a booking)

confirming that the booking has been made and giving a print of the details.

How I can produce a report based on just the current booking information just entered in the form?

Can anyone please give me some advice on this one, I know how to produce general reports but cannot see how to link one to a current form's data?

Regards

Peter

View Replies ! View Related

Population Current Record With Information From Next Sequential Record
I need to write a query which populates an empty field in the current record with information from a specific field in the next sequential record. Any ideas?:confused:

View Replies ! View Related

Can I Use Combo Box List Out My Record With Going To The Current Record ??
Hi all,

Another problem bothering me. Wish someone can help me to solve it. I am doing the 'Edit' Option right now ... As a user friendly propuse, I had try to use a combo box to let user to choose my employee record with all the detail on but the problem is the program 'JUST' list the record they can not go to the current record!! When i try to edit my record, the program is not allow (for sure because they can't have a duplicate record on it). The reason is because the record just stay in the FIRST record but can not go to the current record. I do believe is the problem of i using the way of listing the record as below:
Me.EmployeeName = Me.EmployeeName.Column(0)
Me.IC = Me.EmployeeName.Column(1)
Me.Nationality = Me.EmployeeName.Column(2)
Me.Race = Me.EmployeeName.Column(3)
Me.Sex = Me.EmployeeName.Column(4)
Me.FMU = Me.EmployeeName.Column(5)
Me.Position = Me.EmployeeName.Column(6)
Me.SectionField = Me.EmployeeName.Column(7)
Me.DOEmploment = Me.EmployeeName.Column(8)
Me.DOResign = Me.EmployeeName.Column(9)
Me.Remarks = Me.EmployeeName.Column(10)

So, Could someone help me to solve it like when i can use the combo box to select the Employee record with the details are on as the same time can go to current record... Thanks alot!!!

i do have the confident if i can go to the current record then my edit option can be working properly.

P/S: I had attach the picture... showing my program problem (with the red circle)

View Replies ! View Related

Need Form For New Record/edit Current Record
I have a table called tblFinishedGoods. There are 3 fields in the table:Serial Number, Model Number and Location.

I would like to have a form that will take input from my barcode reader and input the data for the 3 fields, if the serial number is not already in the table.(I have this already working).
But now I would like the same form to also check the table for any serial number that is scanned in...and if it is in the table already, have it bring up the model number and location for that record.

I'm thinking maybe there is something I can do with Serial Number afterupdate. Basically I'm looking for a way to not require the user to input anything via a mouse or keyboard.

If the 1 form could accept the 3 scans:Serial Number,Model Number and Location, and either 1)enter a new record if that serial number isnt in the table or 2)find that the serial number already exists and overwrite the model number and location with whatever the next 2 scans are.

Thanks

View Replies ! View Related

Faxing Current Record Or Record Set
hello,

I have faxing setup through the XP wizard, I setup my report in Access to use the Fax as specific printer. So When I select a record, it generates the Fax wizard which then prompts for the senders name Fax # etc. and away it goes.

that is ok but It would be nice if I could use the Fax number from the record I am sending, either current record or all. I guess the main trick is how would I pass the fax number to the Fax Printer interface?

Is it possible? I've read a few articles on Microsoft Fax for workgroups to work with Outlook, Outlook help says to go to Office update and download it but I can't find it there? Also do the newer versions of Outlook/Access have a better means of doing this? I am running office 2000, but I can get 2002 installed if that is the case.

thanks

Ziggy

View Replies ! View Related