Forms :: Getting Data From A Query For A Field On A Form
Mar 3, 2014
I have a database I use for tracking IT problems (tickets). The form I use has a field to select who the ticket was opened by. To select this, I created a query that shows only active contacts (users) and set the opened by field to use this query to select the user. It works great except for the fact that once a contact is no longer an active user their name on the opened by field is blank and no longer shows on any old tickets they had. Their name is still associated with the ticket though. I know this because I have the opened by field set so when it loses focus it opens the contact record (form) of the user so that we can update their data. So I find it weird that the opened by field on my old historical tickets wont show the name of a user who is no longer with us but the field itself knows who it was and brings up the contact form for the non active user after lost focus.
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I have a form that is bound to a query. The user inputs their id in a text box and the form should find the corresponding record with 5 different pieces of data displayed in separate text boxes. There is at any given time only one record set per id. The query has in the EmpID the criteria: [Forms]![frmProdStopEntryNew]![EmpID] to reference back the EmpID as the filter.
On the form, after the employee enters their ID, the afterupdate should pull-up the record that corresponds to that id. The code for the afterupate is:
Code: Private Sub Emp_ID_AfterUpdate() Me.Filter = "EmpID = '" & Me.Emp_ID & "'" DoCmd.RunCommand acCmdApplyFilterSort Me.StopTime = Now() ReasonID.SetFocus End Sub
I also have a form load to go to a new record just so that the form is blank when the user enters it.
Code: Private Sub Form_Load() On Error GoTo Form_Load_Err DoCmd.SetWarnings False DoCmd.GoToRecord , "", acNewRec Form_Load_Exit: Exit Sub Form_Load_Err: MsgBox Error$ Resume Form_Load_Exit End Sub
The problem I'm experience is that the query to find the record has a 50/50 hit and miss success rate. I think this is because the form load gets activated each time the query is refreshed afterupdate. And the form load has the new record syntax so it sort of nullifies it each time. How do I fix that?
I have a basic invoicing setup, with a Form (Invoices) and subform (InvoiceDetails).When in the subform, i have a combo box to choose a Product Code (saved in table as PCode).I want now to auto fill in the NettPrice and (Product Description) PDesc fields in the subform row - by looking these up in the Products Table and entering the data into the relevant fields on the Subform. This lookup will be based on PCode.
I tried all sorts of methods and the one i favour, if i could get it to work, is setting up a Function then calling this function from the Default Value property of each field involved.So, for the Product Description field (PDesc), i created a Function as follows:
Function GetDesc () As String GetDesc = DLookup ("[PDesc]", "[Products]", "[PCode] = " & Forms!InvoiceDetails!PCode) End Function
Then i try to call by entering =GetDesc () into the Default Value property for the PDesc field.I seem to have a syntax problem with my function code.I know some of my values like NettPrice need not be fields on my Invoice Details table, but the prices change and I also need to be able to overwrite prices etc when typing invoice.
Is it possible to use Filter option in my form without using Query Wizard? - So that the user would be able to edit and change the other field on the same form
The code below always displays opens form even when field being tested is null.
Private Sub Assign_Classes_Click() On Error GoTo Err_Assign_Classes_Click Dim stDocName As String Dim msgboxstring As String Dim stLinkCriteria As String Me.name_found.Requery
I have a form puling from one table and then a subform in that form pulling from another table.I have a field in the [FORM] called [GROSS REFUND] and a field in the [SUBFORM] also called [GROSS REFUND].I want the value of [FORM].[GROSS REFUND], if the value is not zero, to be copied into a new record on the [SUBFORM.[GROSS REFUND].
My intentions are to get my combo box to display data into a field in my form, when it is changed/selection is made. So far my form has the following:
Combobox: CBOCharacters Field: CharacterGender
Both of these are in the same table; tbl_Characters. The data type for CBOCharacters is a number and its called CharacterID in the table.
So far I have created this piece of code:
Private Sub CBOCharacters_AfterUpdate() Me.CharacterGender = DLookup("CharacterGender", "qry_StillNeeded", "[CharacterID]= " & Me.CBOCharacters) End Sub
When I run this event procedure i get an error:
Runtime error '2471'
The expression you entered as a query parameter produced this error '[CharacterID]'
I have a subform with continuous records. One of the fields in the recordset of the subform is a field named "Remarks". This field does not need to be visible on the continuous subform as it is rare that this field will have any entries.
I plan to apply conditional formatting on another field (IDcardNo) in the record line of the continuous subform so that when field "Remarks" contains any data it will show as a different format on the field IDcardNo.
I would like to make a small form appear when one points to the IDcardNo field with the different format, so that the data in the field "Remarks" pops up when one points to the field IDcardNo with altered format, showing that there is data in the field "Remarks".
I've two tables, QA (Quality Assurance) and Instruments. I'm using form to add data to QA.
There's a field in QA, named InstrumentUsed. The criteria is that InstrmentUsed should only accept value when 'status' field in Instruments table shows 'working'. If status is 'faulty' or 'need repair', it should pop up the related error message and cursor stays on the same field.
I've not starting learning VBA yet, so I'm trying to make use of Macro's.
How to use data from another table as a validation criteria for a field in form?
Pops up a MessageBox and stays on the same field, on the form, unless error is resolved?
I have a form that contains two text fields i.e.; [Scat_text] and [STyp], which each can be changed by the users. I would like to combine the results of the two fields together and validate if the results exists in a query. What would be the best way to accomplish this?
productId ( which is filtered from combo boxes on main form) . productId contains two columns 1 bound column(id) ( hidden with width 0 ) 2 column (desription) ( shown with width 2 )
Now after entering the data when i come back to the form again . It hides the data from the productId field on subform , because data in combo box (on main form) doesn't match. Although the row is showing up , only the text in field (productId) is hidden.
Now when i change data in combo box on main form to match the ProductId , then the productId field text shows again. after changing combo box it hides . What i need is the existing data should not hide when i change the combo box selection.
see pic 1 where product matches and pic 2 where not. please note i am using master child relationship on the form/subform.
Display data from Query into Form..I am attaching the database dbtest.zip which contains a table, couple of queries and form.I have done what ever settings I could after going thru some tutorials / discussion forums online, but to no avail.
All that I want is, when I open the form "frmtest" in "form view", and select CS in Combo 8, the corresponding fields in the form viz Item name, Sl No and System Name should get updated on the form. I want to view it in "Single Form" format.
I have a form that displays employee details. I've also created a query that calculates the current age of all employees and updates automatically. I've also created a subform that displays the query data.
My question is, how do I get the query data to display on my main form? Can I do it directly from the query or do I have to use the subform? There are multiple columns on the form and I want to add it as a new column that displays the same as the rest but continues to update automatically.
I want to make a query([query1]) based on a table([table1]) and a field from the [form1] then I want to make another field in [form1] and bound it to [query1] all the fields are number (and double in [table1])
I have a date picker field in my form that is used as part of a criteria in a query. I also add it as a result field in the query being that it is appending data to a table. But when the query runs it puts weird characters into the query field with expression.
The Expression is as follows:
BillWk: [Forms]![Invoice Form]![WkEnd]
My Criteria is: Between [Forms]![Invoice Form]![WkEnd] And [Forms]![Invoice Form]![WkEnd]-6
I am using Access 2007. Second, I am using two tables, Inventory and Service Request. Inventory is a list all the Inventory, with has things like location (building & room number), type (desktop, laptop, etc.) and access tag number. Service Request also has Access Tag Number (should link back to Inventory) & date of reported/resolved problem and description of problem/solution.
On my form for Service Request, I have the access tag number as a fill text box, when you double click on that text box, it runs a Query that asks for the 1) Building, 2) Room Number & 3) Type . . . Query currently opens in a the spread sheet view that shows those three things but also the Access Tag Number associated to them.
I would like that Access Tag Number to just return into the field that was double click to start the query.
In short can I display a field on a form that is not in the forms field list without using a sub-form? I am trying to show a value from a query on this form. I tried this in the control source: [qry_op500_entry_delta]![countofphone model] I get an error stating that this is not in my field list for the form. If the answer is No or if needed I can give more information on what I am trying to do.
I have a table that holds the SQL texts for ca. 1000 Select queries (mostly minor variants that are used to programmatically swap out RowSource strings for combo boxes). I'd like to have a quick and easy way to open/review/modify these queries.
One strategy would be to display the SQL strings in a field on a Datasheet form, then use an onClick event on a text box linked to the SQL-holding field (or perhaps an onClick event tied to an unbound text box on the form) to open the associated query. That would allow me to view the SQL of the query that I want to open, allow me to quickly scroll through the list of stored SQL texts, and give me options for sorting or limiting the SQL-texts displayed in the datasheet form.
But, I can't seem to get the onClick event to work. The problem seems to be that I can't figure out how to pass the SQL string contained in the field to a function that will use that string to open the query .
The subform 'PersonID' populates however the mainform 'PersonID' does not.
I just need to write code, or maybe do something that will copy the number from subform and paste it in the mainform field, maybe at the click of my save button.
I have a form with a subform. On the subform I have a field that has customer number in it, on the main form there is a field that will need to say "none" if the field with the customer number is empty, and empty if the customer number field is filled in. the field will not print out in the letter if there is no data on it.
the customer number comes from the table "CustomerNumbersData" the the field that needs to be empty on the main folder comes from table "CustomerData"
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
