I'd like to set up a form that allows you to enter a unique number for a record and then automatically change the value for one or more fields in that record.
I have a large number of records that I only need to add values to two fields so am looking to make a shorter form with the ultimate ail of doing this with a barcode reader.
I thought I could just add a line like:
Me.controlvaluetochangename.Value = "newvalue"
to and event property like after update and it would change the name for that record. Instead it created a new record with that value.
PROCESS AS DESIGNED: The user logs in and is given a form displaying existing records that are coded (by userid) for that user to update. From that list, the user selects a record and continues to a second form (the first form closes) which displays all the fields of the selected record which should/can be edited by the user (plus a few non-editable key fields). There is nothing tricky to how the records are selected for display. A simple query behind the first form selects data records using the userID number. The second form does a simple record select using the active record ID number on the first form. Nothing tricky -- no modules just queries, tables, forms and simple macros to string it all together.
The user can open/close these two forms as often as necessary and the record will continue to show up until a value is entered into a "completion date" field. When that field is no longer blank, the record will no longer be displayed for use.
PROBLEM: About 90% of the time, things work as expected. But 10-15% of the time, somewhere between opening the record in the second form and closing the second form, the edited record totally disappears (as if it were never opened, it isn't even system date stamped) and the input information is inserted instead into a brand new record.
Users can sometimes tell when the edit record is dropped during processing, because key fields displaying previously entered data go blank and the displayed recordID (autonumber) is changed. p> ERROR PATTERN: NONE! It can happen as the edit record is first opening, in the middle of editing the form, or at the end when closing the form. It will happen on a specific PC 2 times in a row and the third time be fine. It has happened at multiple locations but not for all people and not consistently for the same person/same pc. The same record that failed can be re-selected and work just fine the next time.
I cannot reproduce the error myself. I have tested everything I can think of without causing the error. I can lose changes and avoid a system date stamp if I abort my Access sesssion, but that doesn't create/insert a new record with my new input. It just loses everything.
I have wondered if a momentary break in the network/communications could break the connection to the application to just enough that the form remains on the screen, but selected record is discarded. Input into the empty form would/does insert a new record.
I have asked the IT Operations group to investigate if a network/communication problem could be the culprit, but they haven't gotten far on this. I have never seen this happen before and am totally stumped but am desperate to resolve it before user confidence is damaged.
I have a table called tblFinishedGoods. There are 3 fields in the table:Serial Number, Model Number and Location.
I would like to have a form that will take input from my barcode reader and input the data for the 3 fields, if the serial number is not already in the table.(I have this already working). But now I would like the same form to also check the table for any serial number that is scanned in...and if it is in the table already, have it bring up the model number and location for that record.
I'm thinking maybe there is something I can do with Serial Number afterupdate. Basically I'm looking for a way to not require the user to input anything via a mouse or keyboard.
If the 1 form could accept the 3 scans:Serial Number,Model Number and Location, and either 1)enter a new record if that serial number isnt in the table or 2)find that the serial number already exists and overwrite the model number and location with whatever the next 2 scans are.
I have a form that assigns an Auto Reference Number to each record. Now the records are later edited. Is there a way that I can have the user enter the Reference number and come directly to that particular record that needs to be changed in the form. Like a dialog box that asks Enter Reference Number or something.
I have the following code on a combo box in a form that creates a new record in the table Products if it doesn't already exist:
Code:Private Sub comProduct_NotInList(NewData As String, Response As Integer) Dim strSQL As String 'Exit this sub if the combo box is cleared If NewData = "" Then Exit Sub strSQL = "Insert Into Products ([Product]) " & _"values ('" & NewData & "');" CurrentDb.Execute strSQL, dbFailOnError Response = acDataErrAdded End Sub
It creates a new record and inputs the appropriate value into the Product field, however then it goes to the next record and when I try to edit other fields, it does so on a new record.
So, for example, I wanted to set the Product field to ProductA and the Brand field to BrandB and the Size field to 200, it produces two separate records like this (code box used for formatting):
I have a continuous form with unbound controls. These controls write new records into a table using rec.addnew
If possible, I would like for the user to only be able to add/edit the new record only and not to edit the continuous form below. When I turn off edits on the form, I only get the new record to show. I found some pages that reference using code like .allowAdditons, .allowEdits and .allowDeletions but unsure of how to implement them since I have more than one control the user is entering data into. I also found another site that says to use a control to determine if the record is writable to: I do have a control that is set to =Date() on the default value.
I have created a form (races) with a subform (yachts in a race) to edit data in joined tables. It seems to be working well but I am having difficulty selecting which record to edit in the main form. I can scroll through the records until i get to the correct race but this won't work well when there are a large number of races to choose from.
I can see that I can use linked forms to select a race from a multi form or datasheet and call up my desired form by a double click on the ID field. This should work but isn't really elegant with users scrolling down a long datasheet to select the race record they want to work with. Is there a better way?
Races are uniquely identified by 3 attributes: SeriesDivision, Date & RaceNumber.I have created an index in the Race table for these 3 fields which forces them to be unique.I would love to create an interactive routine which asks users to select from a list for each of these 3 attributes and then returns the form for editing with the appropriate race record selected or even cascading - users select a SeriesDivision can then choose the valid dates for the selected SeriesDivision and can then select the valid raceNumbers for the selected date
I have a form which displays the records from a query (i have included a picture of this form) and allows me to filter them and create a report from the filter.
I'd like to be able to select a record with the record selectors (or preferably without) - and then go onto edit it in a new window and save the edit...
If possible also to select a record with or without record selectors and click a button to delete the record
I'm using the following code to allow the user to pick a record from a continuous form and edit it in a new window. For some reason my where condition isn't working properly as the edit form always opens to the first record instead of the record associated with the "Edit" button that was pressed.
Code:
Private Sub lblEdit_Click() DoCmd.OpenForm "frmEditPlants", acNormal, , "[PlantID] = " & Me.PlantID, acFormEdit, acDialog End Sub
I have a form with a duplicate record button.Sometimes when duplicating a record the duplicate is created but the form remains in edit mode (small pencil in the form margin).I also have a manual record counter on the form which does not refresh to the newest record number following the duplication? Both issues happen together and not independently.
I now have the standard MS Access record counter visible on the form to see whats happening and this always jumps correctly to a new record number when duplication is triggered, so when the problem happens the MS Access counter is showing one more than my own record counter.
If I use the standard MS Access record navigation buttons and go back and then forward by one record, the duplicated record is then corrected. The edit mode pencil is gone and both record counters read the same, which I assume has happened this has forced a save to happen
If Me.Dirty Then Me.Dirty = False End If
- in the current event of the form, would solve the problem, but it doesnt make any difference at all, its still no better, or no worse than before?Using CTRL + S removes the pencil, but doesn't correct my bespoke record counter?
I'm working on a database for my school that is designed to keep a record of each staff members activities that they go through during the year. With the activity information, all their expenses for that activity are listed (see database for more information). Anyway, for anyone who has the time to look at it, here is what I am having trouble putting together:
I have done everything except create budgets. I would like to be able to set a yearly budget for each department (Maths, Science etc.), with the ability to create a mini school budget (Junior School, Senior School etc.) from the sub department budgets. Laid out like this:
Budgets: - Junior School - Middle School - Senior School $8050 (worked out from the total of the department budgets below) -- Maths $2000 -- Science $3000 -- English $3050
What I would like to be able to do is get the Total Expense for each activity that each staff member has undertaken, and subtract that from that staff members working department (shown in database). I would then like to be able to create a report, showing:
Departments Budget How much the department has spent How much they have left in their budget
You can then use the figures for each department to get these values for an overall Senior School budget:
Mini Schools Budget How much the Mini School has spent How much they have left in their budget
Does anyone have any ideas as to how I could tackle this?
I am creating a small Access application that will allow me to update my Call handling system's site table. It uses an mdb file, and I wish to add new sites (from new contracts) to the SCSite table.
I recieve regular updates of new sites from existing customers, so want to create a simple macro that will
1: import (from an excel file) the new sites and add these to the existing SCSite table 2: Check for duplicate records using the Site_Num primary key 3: Merge non duplicate records into existing SCSite table.
Is this possible, and if so - how? VBA? Macro builder? Query?
A Swift response would be great, I have had a look through previous posts - but to no avail!
I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?
The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B
if it is possible, are there some requirements that have to be fullfilled?
I am trying to create a form to enter data in a table. I would like to make it pull in info from a switchboard. If the record already exists I would like it to find it and allow me to edit the info. If the record doesn't exist I would like to be able to add a new record with the data input. What is the best way to accomplish this?
I have a form 'Players' which has a field called Contact. When adding a new record to the Players form, the user has to select an existing contact or add a new one (they cant just type in a name).
(There is a contacts table)
So, i want a button next to the contacts field.I want it to:
1. If there is already a contact selected on the form, the contacts form open at this contact, so can be edited.
2. If there is no contact selected, the button will take the user to the contacts form, but will display the first record in the contacts table. From here the user can select the contact they want.
This is what i have (code below). With this code, if there is a contact already entered, the contacts form loads with that contact select, which is correct. However, if no contact has been selected, the contacts form opens with a blank record; whereas i want it to open at first record!
Private Sub Command90_Click() On Error GoTo Err_Command90_Click
Dim stDocName As String Dim stLinkCriteria As String
I Built a form with a selectbox. On each form there a several records shown. After each record you see a selectbox wich you can turn on and off. Now, everytime I click on a selectbox it automatically goes to the first record on the screen, why is that? With this action I want the currentrecord to be modified, but can't figure out how.
I think it has something to do that an 'edit mode' is selected.
I'm new user here and I use Microsoft Access 2003 for my applications.
I use an mdb on a server (datas) and form on station over the network (application).
I would like to lock the record edit by user when this user press "modify" button and unlock the record when this user press "save" button to avoid with the other users modifying the recording.
I have a form based on a query that selects only the people working in a single office, and displays various personnel data items. All of the data is contained in a single table. Whenever I update any of the data in the form, I get a message saying that another user edited and saved the data before I edited the data. Its rother annoying, and I can't figure out why it is happening. The form is bound to the query, and a list box contains the names of the folks. A subform contains the personnel data, and the two are connected by the primary key.
I have a form that has a drop-down box. When the form opens, all the records are shown. Once a name is selected from the drop-down, only the records associated with that name are shown (a filter is created using code). The problem is that once the selection is made, I am unable to edit the records. I try to click in the fields but am unable to.
I have inherited a partially completed membership Access database which I understand was originally generated from a load of linked FoxPro tables. There is a form which uses multiple tabs, the first tab contains general membership data (ie name, age etc) and this pulls data through from the 'Main' table in the database. Each of the other tabs includes a subform which shows data held in a number of different tables linked to the 'Main' table (eg, first tab - general data from 'Main table', 2nd tab - home addreses from 'Home Address' table, 3rd tab - business addresses from 'Business Address' table). All tables are linked to the 'Main' table through a common 'RegisterID' field. When I open the form I can query on any of the records and amend and items of data on the various tabs them without any problem. However if I add a new record using the >* option from the record navigation I can add data to the first tab (ie. into the 'Main' table) but if I move to the 2nd or 3rd tab I am unable to edit/add data to the fields on those tabs. But if I save the record after completing the fields on the first tab and then close the form and reopen it I can then edit the 2nd and 3rd tabs, can you suggest how I can change the setup to allow editing/adding data to the 2nd and 3rd tabs without having to close the form and reopen it please. I have checked the subforms and they are set to AllowEdit and there are NoLocks.
I am trying to double click on a subform field called Address which would open a form frmEditWorkItem (based on a query qryWorkItem) then to be able to carry out edits for the current record.
I am attempting to create an attendance database that needs to be able to be updated if someone calls in sick.
I have created a cascading combo box where you can select a date up to 15 days into the past with the following code in the rowsource for cboDate
Code: SELECT DISTINCT tblAttendance.dataDate FROM tblAttendance WHERE (((tblAttendance.dataDate)>Date()-15)) ORDER BY tblAttendance.dataDate DESC;
an after update event has been coded to show only employees who have been scheduled for the day
Code: Private Sub cboDate_AfterUpdate() On Error Resume Next Me.cboAgentName.RowSource = "Select tblAttendance.agentName, tblAttendance.attendance,
[Code]....
So basically what i need to do is be able to use the cascading combo boxes to select the date and employee to go to that specific record so i can update the other fields. Currently when i attempt to do this with my current form, it automatically goes to a new record when i select a date and name and doesn't update the existing record.
I am trying to create a duplicate of a certain record in a table so that the user can then edit the new record - or old record even as it doesn't matter - as long as one of them is original. The idea is that any revisions / changes are stored the user may of made.
