I have a datasheet form that is used to update existing data in a table. One of the fields that can be udpated is Department. On the table I'd like to update, the DepartmentID is stored. The DepartmentName is stored in a seperate table.
I would like to use a combo box so users can select from a list of Department Names, then update the DepartmentID field in the main table. However, it appears the combo box can not be "bound" to the DepartmentName field in the Department table - every time the combo box is changed it tries to update the Department table. If I make the combo box unbound, no data appears in the combo box so users can't determine what the current Department is.
Using the Form View I could set a default value for the combo box based on the current record, but that doesn't seem possible in the Datasheet View. Is there a way around this?
i have a form with two cascading combo's that work when the form is opened as a single form however when i place the form on Page 3 of a TabCtl it fails to cascade the correct values.
frmSiteDetails > TabCtl33 > Page3 > frm ConditionDetails subform with Combo's 1 & 2 Combo 1 = [ElementGroupName] SELECT ElementGroupID, ElementGroupName FROM tblElementGroup; has Column Count of 2 and Bound Column 2 Combo 2 = [Element] qrySubElements has SQL:
How to correct the issue below. I created a split form and I have 2 combo boxes that allow multiple selections. The one combo box for LOB (line of business) works perfect and does not create duplicate records in the datasheet view of the split form. The 2nd combo box with multiple selections creates duplicate records in the datasheet depending on how many selections are made.
I have checked this in the underlying table and there are no duplicate records, it is only in the split form datasheet. I have checked settings and configuration between the 2 combo boxes that are reacting differently and they appear to be identical...
I am having an issue trying to show all records when I load my form. It loads correctly when I don't have a record source for the main form. However, the combobox filters will not work. My goal is to have users be able to use the datasheet, subdatasheet and combobox filters. I can get the form to work just fine when I link the Main form and subform, but when the form opens it is filtered on the first record. I have been successful with this approach when using other forms, but they didn't require a subdatasheet.
Is there anyway I can have the comboboxes to filter yet be able to show all records until the user selects filters? Is this possible?I finally got my Manager to agree to use a database instead of Excel files based on this form setup and I need to make as "user friendly" as possible (look and function like a spreadsheet) I attached some pics of the form along with the code for the filter.
I have a form for inputting products into a table called "products". On this form is a combo box which lists the Suppliers. This list is generated by a Table called "suppliers".
If the supplier has not alredy been entered into the supplier table then it is not listed in the combo box list. I have added a button to open a supplier entry form to add the supplier to the supplier table. Once the new supplier is entered and the supplier entry form closed, the new entry does not show in the combo box list unless you close the product form and reopen it.
How do I refresh the combo box list without closing the form it is in and reopening it.
I have an unbound combo (because it is synchronised with another unbound combo box) box that selects a record, but I can not get it to update the rest of the form to show the required data.
The form looks up project data from 3 seperate tables which are all linked by relationship.
I have a small issue with the before update event, it is triggered for every record of my subform in datasheet view rather than just once when the user leaves the subform.
I have a module which is called from my form and all the subforms. It tells the user that changes have been made to the record and gives them the option to undo. This is to prevent accidental editing when viewing records. This works fine, but sometimes the user must enter a number of records in a subform and so they must answer the message box every time.
Is there any relatively straightforward way to accomplish the warning and undo when the user leaves the subform rather than the individual record in it?
A quick attempt at lost focus and before exit demonstrated that these were not the way to go.
For reference, here is the before update code and the module
Option Compare Database Option Explicit
Private Sub Form_Before Update(Cancel As Integer) On Error GoTo Err_Handler
Public Sub Confirm_Change() On Error GoTo Err_Handler
Dim Msg, Style, Title, Response
Beep
'Define message box Msg = "You have made changes to this record. " & Chr(13) _ & Chr(13) & "Click 'Yes' to save changes " & Chr(13) _ & Chr(13) & "Click 'No' to undo changes " Style = vbYesNo + vbExclamation + vbDefaultButton1 Title = "Record changed - Confirm save" '
'Check which button pressed Response = MsgBox(Msg, Style, Title) If Response = vbYes Then MsgBox "Record has been updated with your changes", vbOKOnly, "Record saved" Else DoCmd.RunCommand acCmdUndo End If
I can't work out how to update the selections available in a combo box as follows: I use a combo box in a main form to select a record (yacht) If it's a new yacht then I use an event linked to a button to call another form to add the new Yacht On exiting the Yacht Maintenance form I return to the main form The combo box in the main form doesn't list the yacht i just added unless I exit and re-load the form
I'm not sure of the best way to refresh the combo box selections or main form without exiting.
I have a create new record from with Employees information taken from tblEmployees. On that form I have a dropdown box of company names taken from tblCompanies. How do I get that to input a ClientID into tblEmployees when I add the new employee? At the moment it just adds a new employee but leaves the ClientID box blank.
I have a simple form with a combo box linked to a field on a query and command button with vb code to open a report based on the value of combo box. The command button also includes code to requery the value of the combo box so that what was already generated in the report will not display when generating another report. Since the requery is tied to the command button, there is a delay in requery of the combo box vallue unless I click the command button twice to remove the report already generated. how to apply the requery to the combo box so that when I click my comman button, the requery process will work without me having to click the command button twice?
Ok So I've created a Database to track phone calls coming into a call center. (Originally created in Open Office, but then we switched to Access)
I've got a form that has buttons to run multiple reports.
The Reports pull data from a table based off dates found in another table (Something I used in Open Office)
Table 1 has all the phone calls
Table 2 has a From date and a to date
The report pulls the phone calls in table 1 between the dates in table 2. In open office I used Subforms (Which were seemless) and combo boxes to update the dates in Table 2 before running the report.
Is there an easier way to accomplish this in Access? If so How? If Not, is there a way to make a seemless subform in Access?
I have a query (SelectedData_Query) , on which a form (SelectedDataQuery_SubForm) in datasheetview is based, which is placed on a form (NAWInvoerForm) where customer adress data is put in. When someone clicks new case button on the NAWInvoer_Form, another form (CaseDateTimeInfo_Form) is opened, data is entered, but when the "CaseDateTimeInfo_Form" form is closed, the data in the subform in datasheetview on the NAWInvoerForm is not yet refreshed. So I tried the following:
Private Sub Form_Close() '------------------------------------------------------------ ' RefreshNAWInvoerForm ' '------------------------------------------------------------ On Error GoTo RefreshNAWInvoerForm_Err
Which works just fine. After the form is closed, the name, adress and cases overview form is reopened and refreshed, thus the case that has just been created is shown nicely in the subform which is based on a query.
But it is starting to become bothersome, sometimes when I close the form in which I create a new case, I don't want to have the other form (NAWInvoerForm) reopening again.
So how can i make it so that when I close the form only the data in the "NAWInvoer_Form" is refreshed, without having to reopen the form again. I mean, in the background, the form is still open anyway. It just shouldn't keep popping up everytime.
It can't be hard, but I can't see it. I'm new at this. I have found some similar topics on this forum, but none which helps me out enough.
It should be something like:
On "thisandthatevent" do a "thisandthatcommand" on "thisandthatsubform" in "thisandthatform"
If I know how the syntax of that is done, it would help me out with some other problems I see coming in the near future. I've looked at, and tried some things based on this link: http://www.mvps.org/access/forms/frm0031.htm But without success.
I have a form named CustomerForm.I have a query named CustomerQ. On my form I have a combo box named combo6.Combo 6 lists all of the company names from my customer table and includes the autokey field which is hidden.When I click on a value in my combo6 I want the values on my form to then be based on the value from combo6 whereas at the moment I select a value in combo6 and nothing happens other than combo6 now displays a different value. how to refresh/ repopulate the data based on combo6 without having to create more forms and queries.
I'm working with Access 2007 and am trying to make a form with multiple tabs that will update when a certain business name is selected in the combo box at the top of the form. I have tried looking up different codes to make the combo box update correctly but can't find anything. Right now I have this code in my After Update:
Private Sub cboBusinessName_AfterUpdate() On Error GoTo myError Dim rst As Object Set rst = Me.RecordsetClone rst.FindFirst "[GrowerID] = " & Me.cboBusinessName Me.Bookmark = rst.Bookmark
leave:
If Not rst Is Nothing Then Set rst = Nothing Exit Sub myError: MsgBox "Record Not Found" Resume leave End Sub
I have 2 combo boxes on a datasheet. combo 2 is depedent on comboxbox 1. But when i work on futhter and click other occurenses, the values change (because of the query looking at the first combobox)
this is very annoying. can someone pleeeeease help, ive been trying for 2 days now
The datasheet is populated in a subform, based upon a table.
Code: SELECT DEV_List.* FROM DEV_List;
Within this datasheet, I have several combo boxes.It is easy to give them a query to show all values possible.
Code: SELECT tbl_PPV_RPM.Createdby FROM tbl_PPV_RPM GROUP BY tbl_PPV_RPM.Createdby;
This gives me a list of all possible values in "tbl_PPV_RPM.Createdby".What I want, is to be able to select only the relevant values in this combobox, based upon a materialnumber in the same row. "tbl_PPV_RPM" contains a row named "Material". The datasheet also contains a row named "Material". These need to be linked. When I try to link them through the query builder, I get this:
Code: SELECT tbl_PPV_RPM.Createdby FROM tbl_PPV_RPM WHERE (((tbl_PPV_RPM.Material)=[Forms]![frmSub_TD_List_Edit].[Material])) GROUP BY tbl_PPV_RPM.Createdby;
But then I get a popup box, requesting for the Material number in "frmSub_TD_List_Edit".So, it does not recognise the Materialnumber in the row I'm trying to select a value.
Is there anyway that we can do cascading combobox in the same datasheet view of subform? Basically I'd like to filter "Account Name" based on "Plan#" selected in the same record of subform.
I'm an extreme newbie, I do not know access very well, nor do I know VBA, I do know PHP.
I have a for in access that has 2 user input fields, one for prodid one for shipid. I have a combo box that upon entering data into the previous 2 fields, it does a query against an MsSQL database looking for a record that has both. In any case there will only be 2 outcomes, either 1 record, or null. I would like to have that same combo box automatically use the result as it's value so that users don't have to check the box, because they won't, and so that the rest of my VBA will be able to function properly.
There is a requirement for 1)Populating a datasheet in a subform with a querystring which is dynamically built in VBA.This querystring is constructed based on the search criteria fields selected by the user in the main form.How can this be immplemented?
2_To click/double click a record in the datasheet,extract data and populate textfields,comboboxes with it which are in the main form.How is this achieved?Also,I can't find click events in a datasheet.
I'm working on someone elses database, and I'm not too familiar with access.
He's got this form called Customer_Select, and when you click on a check box for a customer, it's option value is recorded in a field called Product, which then corresponds to the product/customer id located in a table called CustomerCharacteristics.
The problem is that I just realized that this Product field existed. When I view the form as a datasheet, I see the fields: Product, Lot_Nr, Version:, and Last Update: However, when i look at the field list for the same form in Design view, I get: DateTime, VersionDisplay, and Description.
Does anyone know what's going on here? I need to reference the Product field in a query I'm modifying, but the expression builder can only 'see' the fields that are visible in design view.
I'm working With Access 2002, and have a form with 2-subforms. SubForm-1 is a datasheet which shows a list of records. SubForm-2 shows a list of records in a different table, which are linked to the record selected in SubForm-1. The question is how can I Highlite the entire row in Subform-1 when I click on any field in the row. Is there a way to do this? Any help would be greatly appreciated. Thanks MBeaston
