I want to be able to edit an unbound listbox. The box consists of two cloumn, PersonsName and NoOfDogs. The PesonsName is would be edited from the Contacts form if it needed editing, which is fine as would the number of dogs that they owned. The listbox is on a form that shows how many people are going to an event and how many dogs each person is bringing with them (not always the amount that they own).
When the person says that they are attending an event they may not, at that point know how many dogs they are bringing so the user would update that information later. The easiest place to be able to do that is by editing that coloumn in the listbox. I understand that this means editing the table that feeds that listbox which is fine.
I just want to simply be able to click on the name in the listbox and edit the No Of Dogs for that person.
1. The code below should equal the Current Month 2. It does not close the form if no data.
PHP Code: If DLookup("[CurMonth]", "tblEmpEvaluation") = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmmm ") DoCmd.OpenForm "frmEvalNotice" End If
PHP Code: If DLookup("[CurMonth]", "tblEmpEvaluation") <> Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmmm ") Then 'No Data. NotWorking? DoCmd.Close acForm, "frmEvalNotice" End If
I have an unbound listbox on a form which displays a list of staff, both past and present, based on an SQL query.
I want to be able to differentiate between current and past staff using different colors, i.e. Black text for current employees and red for employees who have left, based on a field (True/False) in the original staff table.
I total novice at VBA. I am trying to code a button to modify (the last) record in a subform list and then add a new record based on values in unrelated or unbound fields on the button form.
The following code is based on the first of two YouTube tutorials (this bit on the edit) and looks like it should work. Except that my Access 2010 with Visual Basic for Aplication v7 does not recognise the type definition Database or Recordset
Code: Private Sub ANOwner_Click() Dim cn As Integer Dim db As Database Dim rs As Recordset Set db = CurrentDb
I have two unbound multi-select listboxes that saves selection to their respective table. It works just dandy. However, whenever I go back to the same record, the listbox is blank and apparently I have to instruct it to "retrieve" the previous selection from the table using the PK as criteria.This is far as I can get, though this code causes several errors and I'm not sure what I need to put in as an array for "For Each" statement. If anyone can guide me how to retrieve the old selection from the table and displaying on listbox, that would be great.If IsNull(DLookup("[ClientID]", "[tblClientAccommodations]", "[ClientID]=" & Me.ClientID)) = False Then Dim RecCount As Integer Dim Index As Variant RecCount = DCount("[ClientID]", "[tblClientAccommodations]", "[ClientID]=" & Me.ClientID) For Each Index In Me.lstReasAcc Me.lstReasAcc.Selected = (DLookup("CommunicationID", "[tblClientAccommodations]", "[ClientID]=" & Me.ClientID)) Next iThanks in advance.
I have a combo box that inserts data into an unbound list box and table. This works great but I am having trouble with the deletion part. I want to be able to dbl click on the item in the item list and delete it from both the list box and table. Currently, my code is deleting ALL items, not just the one item I want to get rid of. Any ideas would be most appreciated :) This is what I have for the deletion code:
Private Sub List92_DblClick(Cancel As Integer) DoCmd.SetWarnings False sql2 = "delete from PROFILE_Industry where " sql2 = sql2 & "profile_id = " & Me!Profile_ID sql2 = sql2 & " And Industry_focus = '" & Me!List92.Value & "';" DoCmd.RunSQL (sql2) Me.Refresh DoCmd.SetWarnings True End Sub
I'm preparing a query as the control source for an unbound listbox. The following code gives the desired results:
Code: SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[my].[control])) ORDER BY QrySbfShotList.CamerasFK
(My.control will be a control on the form. For the time being, I let the query prompt me for a value.)
It produces two columns like so:
CameraNum Camera ID 1 2 2 3 3 4 4 5 5 6 6 7 8 9 11 12
CameraNum is text; CameraID is numeric.
Now, I'm trying to use a trick I read about that should add a single textual entry to the top of the list like so:
Code: SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[my].[control])) ORDER BY QrySbfShotList.CamerasFK
union
SELECT "(ALL)", "Dummy" FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[my].[control]));
This produces
CameraNum Camera ID ALL Dummy 1 2 11 12 2 3 3 4 4 5 5 6 6 7 8 9
The second (numeric) column is now out of order. This is reproducible for other values of my.control. If there are double digit entries they get inserted at the third row.
Why? What am I not understanding about how UNION works?
(BTW, I know I could put the "ALL" entry into tblCameras, thereby avoiding the need for a union, but I'd still like to know why the unexpected result.)
My form has a listbox (lstHeatTreatments - Multi-Select disabled) that displays Heat Treatment descriptions and an unbound textbox (txtHTDetails) that I would like to have display the corresponding memo field when a description is selected from the listbox.
This is my code so far:
Code: Private Sub lstHeatTreatments_AfterUpdate() Dim myConnection As ADODB.Connection Dim myRecordSet As New ADODB.Recordset Dim mySQL As String Dim selectedRequirementKey As Long Set myConnection = CurrentProject.AccessConnection Set myRecordSet.ActiveConnection = myConnection
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1 FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate() 'Dim LocQryStr As String 'Dim r As Integer 'Dim ctl9 As Control 'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
1. On my main form, I have a listbox, I would like to edit the values of the listbox.
To do this, I have a popup form with 2 listboxes, one to have the values of the listbox on the main form, and the other listbox with option values for the 1st
1) how to i pass the rowsource sql of the listbox on the main form to the listbox on the popup form
2) how on closing the popup form, do i update the rowsource sql listbox on the main form from the changed value of the popup form listbox rowsource sql
Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).
However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Coding I can use for this in the AfterUpdate event of the listbox?
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.
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I am running in to a brick wall with this. I have an unbound text box with the control source set to =IIF([text42]=0,0,[text42]/[text44])*100 and in continues to return a #name? error.
I am not sure how to get this expression to work. I have even tried to put =[text42]/[text44] and I still get the #name? error.
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
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
I have a problem. I currently have files from our AS400 linked to access through OCBC. I am not able to edit or add any fields to the file through the form or table feature in access. The permissions on the AS400 are set correctly, is there something to change in Access?
Just 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've got a master form, which is used to search for records, and display links to them in a list box. When the user selects a record, they get the full details of the record on a subform. At the top of the subform, I have two buttons, an edit button, which allows them to make changes to the record, and a "new" button, which allows them to create a new record.
My problem is, after a new record is created, the subform adds a new page. So instead of having record one of 1, it shows record 2 of 2. So if a new record is saved, and the user tries to access another record, nothing appears because the subform stays on record 2 of 2(which is a blank undirtied record). If you press navigation button back it will show the record they selected.
Maybe I could make the new form a pop up form, and pass it parameters rather than making a new form within the subform.
I've tried putting the command DoCmd.GoToRecord , , acPrevious in the subforms Current() method, but I get an error saying I cannot go to the previous record.
Hi, I have the following situation. I have a switchboard form which has 2 buttons (Add And Edit) I have a patient record form, which has 2 fields, (DateRecCaptured and DateRecUpdated) Both buttons on the switchboard open the Patient Record form (one opens it in Add mode and the other in Edit mode)
This is what should happen. If I click on the add button on the switchboard, the patient record form should open to allow me to add a record. The system date should then automatically be saved in the DateRecCaptured field. If i click on the Edit button and edit a record, the system date should be saved in the DateRecUpdated field. If no updates are made, the field shouldn't be updated. If you scroll among records, the DateRecUpdated field shouldn't be updated. When editing, the DateRecCaptured field should remain unchanged.
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 had a form working for ages. It has a main form and eight subforms at the bottom of the form. The main form can be edited in all fields.
I have taken a copy, modified the form added new fields to the main source table and updated the query that feeds the form.
Now (in the copy environment) it won't allow me to edit any of the fields. All of the form control properties are the same as the working model (in the live environment) I can run the query by itself and all is OK
There is obviously some little control that I have inadvertently changed and can't see it.
I have a form that uses a combo box to select the record to edit which then opens the edit form... I keep getting a type mismatch error. The bound column is a text column and I'm wondering if that is the problem because this works perfectly everywhere else in the database. The primary key is the SKU of the product which is alphanumeric which is why I have it set to text...
