I have a table fed form that will not accept editing to the filter statement.
Currently the filter is [Dealer Name]='ABC Company'
This is causing a problem because we have multiple locations with the same Dealer Name. They do however, have different Primary Account numbers.
I thought to edit the filter to [Primary Account]=102
However, after I save everything and go back to form view, the filter reverts to the original.
I'm trying to resolve a problem with a form displayed as follows
MainForm
-->SubForm1 embedded in main form
--->SubForm2 embedded in SubForm1
When I edit subform2, then return to the mainform and edit a field on the mainform, I get an error.
'The data has been changed. Another user edited this record.... Re-edit the record.'
My understanding to resolve this is to do a requery. So I added:
Me.Parent.Requery
Me.Parent.Parent.Requery
as part of the LostFocus event for subform2. However, I now get a Write Conflict error when returning to a field in the main form. I get a dialog box with the option to Copy to Clipboard or Drop Changes. I can select Drop Changes and it will let me edit the main form afterwards.
This is Access 2010 over SQL. how to get the main form to accept the changes made to the underlying subform?
Form info
1st Form Name: Mainform
Text to enter employee ID : empid
Sub form within Mainform: SFTimesheet (continues form)
text to enter: Timein, Break, Timeout
SFTimesheet shows and accept entries for each employee called using the text empid, on the mainform
I wanted the subform to show 7 days /lines starting from Sunday to Saturday of current week and dating the text "Timein" accordingly. When employee enteres an entry on the 7th Line, it should give them a new 7 lines for the new week to begin.
I'm creating an application and I want the users to be able to view a table and filter it, however; they won't be able to edit it. Like I said, seems simple enough, but since I'm new to Access I'm not sure how to approach this.
View 1 Replies View RelatedJust a general query about the best approach to take for an editing form.
I want to have two separate forms, one for adding a new record (have completed this) and one for choosing and then editing a selected record.
I have seen that people use combo boxes to display a list of records with fields. This seems quite difficult to use. Maybe that's the best way though?
How do you apply an edit button to a selected record in this type of situation? I suppose really I need a box that has a list of records with a little edit button next to each one, or can I do something that launches the edit form as soon as you click on a specific record?
I have produced a table, the records from which have to be selected by individuals for auditing purposes.
I created a second table with individuals initials and passwords. This is linked by the initials to the main table. Records on the main table are returned when the query is run.
At first I could not edit fields in the query but changed the recordset type to Dynaset (inconsistent). I am now able to edit the query fields. However, having created a form based on the query, I am unable to edit the fields in the form. This is the basis that the auditors will use to score so is vital. Have I missed any other switches or techniques?
I have a form that I need to use to add new clients to a table in my database, lets call it tblClients. On this form I have a combo box which, when selected, will drop down with the first and last names of all clients in tblClients, as well as their Client ID. Once you select a client from the drop down menu, it populates all of the fields in the form with that clients info (Client ID, fname, lname, address, phone number, etc. etc.).
I need two other things on this form: one button that will save or modify whatever current record is currently pulled up, and one button that will start the process for entering a new client, so basically it would blank out all of the fields and fill the Client ID field with the current number +1.
As of right now I have the form made and the combo box works, in that I have 3 dummy clients in the tblClients and when I select each client from the combo box it will populate the fields on the form with the info. I'm using some simple VBA on the combo box such as
Me.FirstName = Me.cboBox.Column(1)
and that seems to all work, but the problem is trying to save/modify data to tblClients. The only record in the actual table that gets modified seems to be the first record. For example, if I pull up client with ID #3 and change his address or phone number, what happens when I click to save the changes it takes client ID #3 record, overrides it with client ID #1, and then client ID #3 is the same as client ID #1 except with the old information. I'm not quite sure why this is happening.
I have a form with several subforms within (tabbed subforms). I've used the code:
In the Current event of the parent form -
Code:
Me.AllowEdits = False
Me.AllowDeletions = False
Me.AllowAdditions = False
In the Current event of each of the subforms -
Code: (THIS NEVER WORKS FOR ME - RUNTIME ERROR)
Me.AllowEdits = Me.Parent.AllowEdits
Me.AllowDeletions = Me.Parent.AllowDeletions
Me.AllowAdditions = Me.Parent.AllowAdditions
In the Click event of the button -
Code:
Me.AllowEdits = True
Me.AllowDeletions = True
Me.AllowAdditions = True
So the main form is locked upon opening and unlocked with the click of an unlock button. How to apply this to the subforms as well. They just stayed unlocked the whole time.
I have placed a filter button on a form as a filter and written the following on-click event procedure:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdApplyFilterSort
Me.Filter = "ACCOUNT_DO_NOT_EMAIL = 'HS'"
Me.FilterOn = True
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
I have created a form based on a query that joins two tables. When I run the form, I am able to input new data into all fields that are from one of the two tables, but it won't accept new data into the second table. I don't get an error message that might help me identify the problem. The join between the tables is of the "include all records from Table A and only those records from Table B where the joined fields are equal."
Can anyone suggest where my problem might lie? Thanks!
kgm
Hey Folks,
I have a form that has two things on it:
A button to open a query
A list box with names in it
How do I get the query so that it will select all records where the name field matches the chosen name from the list box?
I have a form that is based on a query that returns all the students taught by an indidual faculty member. There are two text boxes which I have added to the form bound to a table used in the query. However when I try to add data (attendance data) is doesn't accept it and simply "pings" each time I try.
View 3 Replies View RelatedI am using Access 2003 and I am having trouble with a Combo Box on a form.
I had a combo box (based on a table), storing the choice in a field in the query and underlying table the form is based on. Everything was working fine, until... I altered the form based on a query. Now when you make a choice from the drop down list you get an error bell and nothing happens.
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
View 3 Replies View RelatedWhen I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
I can understand with placing a filter on a form.I have a table that needs updating. I created a form based on the table, but would like to only see the records that need updating, instead of the entire table.
I have no SQL knowlegde. Is there some simple answer? I would like to filter on one field for the word "NO" ie seeing only the records which contain NO in that field.
On a form I have a:
control called "FilterListBox"
list box called "lstCustomer"
option Box called "optCustomerType"
When the user selects an option in the option box, "FilterListBox" is updated to either "1", "2" or "1 or 2"..One of the fields in the query for "lstCustomer" is "CustomerType" and its criteria is set as follows:
[Forms]![frmPrintHowCustomersPaidInvoice]![FilterListBox]
if "FilterListBox" = 1 the query for "lstCustomer" returns the correct records
if "FilterListBox" = 2 the query for "lstCustomer" returns the correct records
But if "FilterListBox" = 1 or 2, no records are returned.
I have a form (Suppliers) with a button that opens another form (Product List).
At the moment the form (Product List) is set as a pop-up form and when opened lists all products.
I want to filter the pop-up form to only display products for the current supplier displayed in the Supplier Form.
I have tried the following below without success - I have varied this but the filter does not apply ...
Private Sub Form_Load()
DoCmd.OpenForm "Product List", acNormal, , "SupplierID = " & SupplierID
End Sub
I have a button that opens a second form and filters that form based on a text box on the first form(which remains open). the problem is that it does not filter the second form unless the second form is already loaded for some reason.
For example if i click on the button it opens the form Unfiltered then i go back to the previous form without closing the first form, click the button again and it is then filtered .
I attached the screen shot of the macro for the button and there are no other events on the second form.
I have a form that has two different option button frames and 1 text box that I would like my users to be able to use to search/filter for specific records. I chose a form because a table does not work as a user-friendly interface for the data that is being stored. The first option button frame allows the user to filter by Month and is named [FilterDate].The second option button frame allows the user to filter by incomplete and complete records and is named [FilterOption].
The text box is used as a search box and is named [txtsearch].I am able to successfully create macros to apply filters for each of the above options; however, each time one of the options is updated, it removes the previous filter. What I need is creating a macro (preferrably) or vba code (if macro is not possible) so that my users can first pick a month (as this will always be the first thing they do when opening the form), then pick complete or incomplete records within that month if desired. The search box then would only filter records that meet the first two criteria (month and complete/ incomplete).
Is it possible to capture a form's filter? In Access 2013, I have a form (from a template database) listing contact information. I can set filters by clicking on the column headers and selecting which records I want displayed. There is also a button to run a mail merge with Word. I want the merge to only include those records displayed on the form at the time it's run.
View 1 Replies View RelatedI have developed an accounting database which includes, among others, tables for Transactions and Creditors. I want to query the data for a particular time period to find out how much I have paid to each creditor. At the moment I can do this by having a Main Form based on Creditors, and a subform based on Transactions that contains a filter to display only those records that fit into the selected time period.
Now, there are some creditors to whom I have paid nothing during that particular period, so the subform shows no records. That's fine, but as I have several hundred creditors, it's tedious to pan through each creditor to see if I have paid anything.
What I want is for the subform to display records from ONLY those creditors to whom I have paid something. I can view these records with a select query by using the GROUP BY function. However it seems that a form's filter property will not accept a WHERE clause that includes a GROUP BY clause.
How I can program this in VBA, i.e. to mimic a select query's GROUP BY function for use as a form filter?
Hi everybody,
I have a large problem which I can't figure out (well 2 actually but the other is less important):
1) I have a form which brings up the results of a search performed by the user (only contains some fields). From this list of results i would like the user to be able to a) see a full report of that Record (which I've done ok) and b) edit the data contained in that Record.
My results form opens the editing form no problems, however, i cannot edit any of the data contained within it. This only occurs when the results form is open as well so i guess it's because there are two instances of those fields open simultaneously. However, I am reluctant to close the results form because the results will be lost.
2) I've tried several methods to open the form at the desired record:
- using the WHERE condition argument of the OpenForm function
- creating a filter in the editing form that runs in the OnOpen event
These two require the creation of a variable (I'm using the Key field) that is inserted into these bits of code. However, all of my attempts thus far have been unsuccessful; every time it asks for the Parameter in a popup. I've defined the variable as a Public variable in a seperate module but don't really understand this aspect partcularly well; I'm obviously missing something.
- the form currently opens from it's own query that gets the Key Field Criteria form a hidden control on the results form.
Here is my code for the 'Edit' button:
Public stProjectID As String
'Results form 'Edit' button click:
Private Sub btnEdit_Click()
stProjectID = txtID2
[Forms]![frmResults].Visible = False
DoCmd.OpenForm "frmProjectEdit", acNormal
End Sub
'Edit' form open:
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "ProjectID = stProjectID"
FilterOn = True
End Sub
Sorry for waffling on...
Thanks in advance for your help.
Bobadopolis
I am trying to create a (normally simple) application, but not having a solid grasp on the concepts of recordsets. The application is serial number tracking for appliances.
The functionality needs to be able to:Search for serial numbers using partial strings (i.e. serial # "T12395723K39" can be located by entering "3K3")
If the serial number does not already exist, allow for adding it.
If data was added erroneously, allow editing to correct it.
The first 2 points are complete but I am struggling with the last. After a user performs a search, I store the results of that search to a temporary table for easy viewing of applicable records.
To allow someone to edit, they simply double click on one of those records to open a seperate form and edit.
When I displayed the content of the temporary table in the form for editing, I found it was not updating the original table.
When I create a separate recordset, using the values of the record selected, the recordset appears to be empty, therefore not displaying the correct record for editing.
My main table name is "SerialNumbers". The code below is triggered when the user double clicks on the desired record to edit.
Private Sub Claim_DblClick(Cancel As Integer)
'************************************************* ********************
'* Subroutine to take values from search results for editing record. *
'************************************************* ********************
'Initializing variables
Dim ClaimNo, ApplianceName, SerialNo, MakeName, ModelNo, sqlstr As String
[Code] .....
Been trying to use wildcards in fields while Filter By Form instanced in Access 2007.
LIKE, BETWEEN, IN or */% not available?
The form is based on a query. I am guessing this is why the drop downs only show IS NULL or IS NOT NULL as choices instead of the entire unique list existing in the underlying table/query.