Form using dynamic query results

hope this is a reasonable thing for semi-beginner to intermediate person to do.

Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that).

Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer.

Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning.

Is it possible for a form "Offers" to be based on a query that keeps updating like that For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime)

Would you need to have a separate table storing only the most recent offer for each company If so, how would you get that updated all the time

Anything you could offer to point me in the right direction would be very much appreciated!!

Posted On: Monday 22nd of October 2012 05:39:12 AM Total Views:  324
View Complete with Replies

Related Messages:

Entering data in form and updating a querie   (72 Views)
I apologies for this sinmple quesiotn to some of oyu, but being fairly new with Access, Im having problems when I update my form and then print our a querie, to obtain certain information, the new data is missing. HOw can I make sure that my querie gets updated when I update my form.
How to format a field on a form as date ?   (171 Views)
I am using ODBC with oracle tables.. My table has columns startdate and end date which are of type varchar(backend tables)..but when i display it in form it should display it as mm/dd/yyyy format, i tried setting format property as Short date but that doesnt work as my form is based on a query.and all the more my table stores the date as yyyymmdd -19990110, this is the way its stored in table (in varchar) so how do i do this i also tried "to_date(substr(startdate,5,4),'mmdd')" --putting this in the query based on which the form loads but this doesnt work even.. i cannot run this query from access as it says undefined function to_date , but when i run this in Sqlplus it gives me the output but i m not able to get the year part... So how do get the date field in form as mm/dd/yyyy
Add record through form   (64 Views)
I'm trying to build a db for my wife, to keep track of this: Several sales employee's are selling insurance policies. Policies are 'owned' by different company's (Policies can have the same description i.e car insurance) I created the following tables : Table Advisor: (employee) Id (PK) Name Tbl NAW: (Customerdetails who buy the policy) Id (PK) and several address fields Tbl Company Id (PK) ComName Fields with contactinformation Tbl Policy Id (PK) Premium Duration Capital I know there should be some FK fields, but I tried almost everything and don't see the solution Question is, how should I create the relations between the tables so I can have a Form where I can: Add a new customer with this info: Customers details, Which Employee sold it to customer What kind of policy Which company holds the policy Is it possible to enter all this on one form I'm trying to find the answer for a couple of days now and getting desperate.. Please have a look ( at this long post.. sorry for that).. A totally different design maybe
Updating oracle backend tables from MS access forms(dynamically)??   (182 Views)
, My requirement is this... I have to use MS Access as front end with ODBC connection to Oracle 9i DB. The application(forms) should be able to update, delete ,insert records into oracle tables(backend). i have a main form,which has some unique id's and other info about the ids and the subform shows several matching id's for that unique id in main form. the user who uses this application should be able to 1) search for the unique id in the main form such that the subform displays all its matches 2) they should be able to select anyone match and say approve(there can only be one match), then that particular record should be updated in the table.IF I USE A CHECK BOX AND IF THEY CLICK ON ONE RECORD AS MATCH,HOW DO I TAKE THAT RECORD SAY THE ID , NAME ADDRESS AND ALL DETAILS AND UPDATE THE TABLE similarly when they select some other record i should give option of deleting other irrelevant matches in the backend table. the main form and the subform uses the same table as source.updates are to another table, i should also have to put entry into audit table about which record was deleted and which one inserted.. How should i do this i am new to MS access .VBA, any help would be highly appreciated!
Access 2000 :Transferring test from form to report   (87 Views)
I have been trying unsuccessfully to transfer about 20 details describing the name, up to 14 different colours, prices etc and one image of beads from the details displayed on a form to a report. I have no problem displaying the details of one bead using a combo box but then I want to send these details to a report. After that I select another bead and then want to send these details to the report. The report is divided into 12 rectangulars to represent 12 sections of a display box. This tells the shop owners which beads to put in which section as display is important for sales. as there wooden beads, glass beads, plastic beads animal beads, letter beads etc, etc. I have gone into this details so you will understand what I am trying to do. I would be very grateful for help but I am sure will realise that I am technically inept and please when you try to explain things to me to remember you are dealing with an idiot.
Drop down look up list - fill in information   (86 Views)
I would like to have a lookup list (drop down), if an item is not in the list I would like to be able to enter it at that time - how would I go about setting this up. I am new to Access and not sure where to start. Table City: CityID Autonumber City Text Zip Text Also, I would like to be able to type a letter - say W and it would go to cities that start with W (Wauwatosa), rather than having to scroll through the list.
Syncronising 2 forms   (125 Views)
I have two forms in a maintenance database. The first allows users to enter details of the maintenance request and the second allows the administrator to enter cost details of materials etc needed. I want the administrator to be able to click a cmd button and the second forms opens automatically at the current record in the first form. I am fairly new at this gane so some clear guidance would be very much appreciated. Beeky
Converting a file to MDE format!!   (80 Views)
I have a database with 12 tables,8 modules and 20 forms. But when I try to convert it to an MDE file it gives me an compilation error, that the size of the file is too large. Can anyone tell me why
Transferring form data to different fields   (129 Views)
I have created a database to track the number of laptop computers and radio modems that are installed in Law Enforcement applications. I use a form to enter all pertinent data (i.e., Laptop S/N, Modem S/N, etc.) into the table, and have attributed these fields to prevent duplicate entries. Each laptop and modem is associated with a vehicle number. At times, the radio modems are replaced or swapped out between vehicles without replacing the laptops. When this happens, I would like to be able to enter the new serial number for a given vehicle that already has a laptop associated with it, without having to re-enter the serial number for the modem that is being replaced. If this explanation makes sense, and someone can possibly give me some advice on how to make it happen, I would greatly appreciate it. I am very new to this program, but know that it is powerful enough to do just about anything; I just need to know how to make it happen! Thank you very much! Ol' Navy
Automatically update value in the subform from Crossstab query   (410 Views)
, I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student. Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work. I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update. Please help! I would really appreciate if you could help me find a solution.
Subform not refreshing data dynamically!!   (112 Views)
I am using a subForm inside a parent. The subform is in the "dataview" format and it shows the data of a particular table (say 'Processus'). What I want to do is, when I insert a data from the parent form into the table processus, I want the child to display the the new record dynamically... Is this possible Is there a property like child.refresh
Problem refreshing a form   (56 Views)
I have an invoice form that I open and store the line items in a table temporarily. There is a button to edit the customer data that opens another form. I don't close this first form as there are issues with saving the data etc. Anyway, I open the edit customer and at the end when the user hits a save button I do an open of the new invoice form (simply to send it control as it is already open in the background). This works fine in that we are back on the New Invoice form. However, I can't figure out where to put the code that I want to execute (Close the Edit Customer form, requery, refresh). It doesn't hit the On Open and I've tried several other paragraphs Got Focus works, but that is hit too many other times and causes the screen to flicker etc. What Event paragraph will it hit coming back in when I do the Open in the Edit Customer form
Max no of fields in form & report   (113 Views)
sorry if this is a silly question but I'm an access beginner.. I have tried to create a form with about 125 fields using the wizard (fields selected from quieries) once I have selected all fields access goes to the Form view but no fields are displayed. if I go into design view I can see all selected fields. Why can't I see anything in form view Is there a limit to the number of fields that can be used in a form Also, is there a limit to the number of fields in a report if there is a limit.. how can I get around this as I need to show all fields
Performance Problem - 340,000 records   (109 Views)
Hi , First time in this forum I am producing an MS Access 2003 application with linked tables to MS SQL Server 8.0 Enterprise Edition that contains a range of tables populated with extensive amounts of Legacy data on a view only basis. I have gone down the .mdb route as opposed to the .adp route. Bill OF Materials ========= In this particular instance I have created two normalised and related tables in MS SQL Server from a flat file containing 340,000 records of invariable quality and meaning in order to represent a searchable Bill Of Materials in an MS Access form. Using the Linked Table Manager I have linked MS Access 2003 to these tables. tbl_BOM_Header Header ID (PK) Catalogue_Numb Catalogue_Desc In an MS Access form - frm_BillOfMaterials - I have represented all 34,000 records in a listbox (lstbox_BOMHeaders) based on a query qry_BOMHeaders. This listbox will be populated by the result of the query depending on what the user types into a text box at the top of the form tbl_BOM_Header_Item SID (PK) Header_ID (FK) BOM_Item_Catalogue_Number BOM_Item_Catalogue_Desc + (several other sparsely populated variables) I have created a continuous subform for these BOM Items and inserted subfrm_BOMHeaderItems into frm_BillOfMaterials. There are 340,000 BOMHeaderItems records (10x) When the use selects the BOM header records from lstbox_BOMHeaders, the OnClick() event executes the following code: Me.RecordsetClone.FindFirst "[Header_ID] = " & Me![lstbox_BOMHeaders] Me.Bookmark = Me.RecordsetClone.Bookmark which works fine - up to a point. Basically my problem (if you haven't guessed it already) is that the retrieval of BOMHeaderItems for each Bom Header record is at best slow or at worst crashes my MS Access application. Please can anyone advice me based on their experience of either: 1) a modification to this approach that will yield quicker retrieval and stop my application from crashing. 2) a different approach that will yield quicker retrieval and stop my application from crashing I have reviewed a couple of the standard articles such as: Any technical advice, guidance or support would be very much appreciated, Many
Sum formula   (68 Views)
I have the following idea in my mind. The only problem is that I have no clue how to make this work. Anynone know how to make this happen Thx in advance
Adding new row to unbound subform.   (83 Views)
A couple of questions about an unbound subform..... 1) I have an "Add New" form with a large number of controls. It has an unbound subform with two columns (a person's name and their role - both combo boxes). It's unbound because I don't want to save the data until they save the new main record. With the unbound subform, I get one blank row. When I add data to both controls, I don't get a new blank row to allow me to enter another. I've tried adding a "New Contributor" button with this in the event: Me.[Contributor Subform].SetFocus DoCmd.GoToRecord , , acNewRec It sets the focus properly, but doesn't add a new row. Recordset type is Dynaset. ow edit, deletion and addition are set to yes. Any ideas 2) I'll be getting the entered data and saving it with a SQL insert statement. What is the syntax to reference a particular control in a particular row Since I only have one row currently, I'm able to get it this way: Me.[Contributor Subform].Controls![contributor] Once the first question in this post is solved, I hope to have muliple rows. How do I reference them
Add a record to subform   (81 Views)
As you can see I have a form with a subform in it. What I want to achieve is that when I press add new record a blank record will popup in the subform without all the other records you currently see. An example of this is the picture under this. If I then fill in the new record row the record will be added to the main table. I already tried it for myself but the problem that occurs with my macro is the following: The macro has the following design: How can I add a new record without opening the subform
Access elements in a sub form from main form   (90 Views)
I have a form (called DeliverableDescription ) that has the following buttons: close, and save the main from has a sub form (called DeliverableDescriptionsubform) that is viewed as a datasheet also I have a table called DeliverableDescription that has three columns (outline number, name, and group number) the DeliverableDescriptionsubformshows data from DeliverableDescription (only the outline number and name) this DeliverableDescriptionsubform is used to update the DeliverableDescription table (through copy and paste from another source) what I need to do is populate the group number column when the DeliverableDescriptionsubform is updated the group number is just the first part of the outline number (i.e. if the outline number is then the group number is 20) I want to create a command in the save button that takes the string value of each item in outline number column and do some simple string manipulation to it then populate the group number my approach is to have a select query that gives me all the outline number then through a loop iterate through each one, performing the string manipulation and update to the table. my question is how to access elements in a sub form, since the save button is in the main DeliverableDescription form also is there a better way to do this thanks for any help you might be able to offer. Code: Dim ws As Workspace Dim db As Database Dim rs As Recordset Dim OutlineNum As String Dim queryResult As String Dim returnResult As String Dim SearchChar As String Dim Pos1 As Integer Dim TotalLen As Integer OutlineNum = "SELECT DeliverableDescription.OutlineNumber " & _ "FROM DeliverableDescription " Set ws = DBEngine.Workspaces(0) Set db = ws.OpenDatabase("N:\statusReport_Test.mdb") Set rs = db.OpenRecordset(OutlineNum) Do While Not rs.EOF queryResult = rs.Fields(0) TotalLen = Len(queryResult) SearchChar = "." Pos1 = InStr(queryResult, SearchChar) returnResult = Left(queryResult, Pos1 - 1) DoCmd.RunSQL ("Update DeliverableDescription SET DeliverableDescription.TeamLeadNumber = returnResult " & _ "WHERE DeliverableDescription.OutlineNumber = OutlineNum ;") queryResult = "" SearchChar = "" Pos1 = 0 rs.MoveNext If rs.EOF Then Exit Do End If Loop DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Viewing current data of a form in report   (102 Views)
I have created a form from a table which has around 300 records. Every record has some transactions in fields. How can I view report of single data with all fields, which I am currently viewing in a form Also I have locked field for editing data in form. I want to edit data in table directly (back end). So is there any way to edit data being currently displayed in form. I mean if I create a hyperlink of table, can that link open that particular record in table
Choosing different options in a form   (96 Views)
I am trying to make a form that will allow me to make several selections and run a report. I want to be able to select from a list of Grade Levels, 9th, 10th, 11th and 12th. After I have picked my grade level or selected more then 1 grade level I want to be able to choose from a drop down list of school district. After I have chosen my grade level and my school district I want to run my report. How do I do that