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 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 several Suppliers, each of whom holds several SalesEvents. At each SalesEvent I might buy none, one or more Bundles. Each Bundle then contains one or more Items. These relationships are all reflected in my Access 2013 desktop db as one-many relationships with cascaded updates.
My form structure for data entry reflects the Relationships, in that I have a Supplier form with a SalesEvents subform. On that subform I can enter data about each of that Supplier's SalesEvents, and each SalesEvent row has a button to call up a SalesEvent form which has a Bundles subform. Then each row of that subform has a button which calls up a Bundle form containing an Items subform. That Bundle form identifies the Supplier, Sales Event and Bundle number, and within its Items subform I can then enter the data for each Item.
Problem : All used to work fine, but I've clearly changed something because now when I select the Items button on the Items subform in the Bundle form, the system gives me the data for the first record in the SalesEvents table, rather than the one selected. I have checked the raw data and that's as it should be. I have tried to undo all the steps I took since it last worked properly, but to no avail. Restoring the last backup (taken when I'm sure it worked properly) still has the problem.how do I find what data is actually being passed between forms and subforms ?
In a form I have Two fields (which are of interest):
1) EmployeesName which is a lookup from another table named
TBLEmployees that contains two Fields EmployeesName EmployeesNumber
2) Second Field named EmployeesNumber which currently also using a lookup from table named TBLEmployees
if possible what I want to try and do when I select EmplyeeName from the dropdown list of names I want the EmplyeeNumber to be inserted into this field automatically rather than searching through the dropdown list again is this possible?
Is it possible to have a Find & Replace in the table using access forms. ( Like the inbuild option in Excel ), I want the user to search some data & replace with the new data which may present in more than one record.
I want to do it with forms, because i dont want the user to use the tables.
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 am trying to locate a field in the form. The field is existing in the Tab Order list. But when I use the tab to locate the field in design or run mod, the tab never jump to that particular field.
Can anyone tell me how I can locate the field (in order to change the properties of this field)?
I have a form that has a FIND button. I want the user to enter the 3 fields that make up the primary key, then find the associated record. How can I do this in Access - not VBA?
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?
Given the firmname and textbox name is it possible to programmically get the fieldname and table name where the data for that control is held.
I can use .controlsource and .recordsource
But is possible that .controlsouce is an alias of the actual fieldname.
Similarly the recordsource could be a query, from that I want to get the actual table, complicated say if two tables in the query had a field with the same name (even if only one was referenced)
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.
I'm using a split form with the datasheet on the left.
When I use the 'Find' in the Navigation buttons at the bottom of the form, it refuse to find text in a particular text box.
The text box is a memo field and is enabled.
Example:
I have text in the memo field which is a title of an episode - "The Outcast."
When I start typing "the" it finds this text in the memo field. But when I type the entire title it the finds the text in another field. ie "out."
I've tried the 3 different Search setting in options. When I use the Find command from the ribbon it work ok as long as I select the text box to search in first.
I would like to be able to switch between these two forms and the form automatically set focus to the record that I was on the form prior. Meaning if I am on record 3 on Form A and navigate to Form B, I would like to automatically set focus to record 3. Is this possible?
A possible issue after this gets sorted out is what happens if with the same scenario above record 3 does not exits? is it possible in that instance to simply requery the form?
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria: ID - primary key DiscountID - only when the DiscountID = 92 Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria: ID - primary key code = text field with a code like "einstein01", "einstein02" Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
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 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