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 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 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.
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 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 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'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.
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 need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database Option Explicit Private Sub AttachPaymentDetails() Call PerformInsert("tblFinancialBudget", "frmFinancialBudget") End Sub
I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.
This is so the user knows that what they are entering is roughly in line with what has come before.
So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
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 need to write a query which populates an empty field in the current record with information from a specific field in the next sequential record. Any ideas?:confused:
I have a query to bring in values, I need to select 2 rows of data but the criteria is as such:
x= starting value on form
now the row of data must match the following criteria previous row to current row(ref temp)<=x And Current row(ref temp)>xnext row to current row(ref temp)>=x And Current row(ref temp)<x
I have dealt with SQL before but how to do the above.The isolated 2 rows of data will then go into unbound boxes on a form from which I will do intercept and gradient calculations.
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 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 have a form in access that opens to the first record every time. This form will be constantly updated and i was wondering how i can set it, so that it opens up to a new record everytime you open the form.
I would like to create the following functionality on a form in my database:
When the form is open, and a record is displayed on the screen, I want to add a button that will open up a second form displaying information related to that record.
To be specific, I have a form called "Contracts". Each contract record contains a customer name in the field "Customers." I want to create a button labeled "Customer Details" that will open a new form, "ViewCustomers" displaying the record associated with the value entered in the "Customers" field for the current (displayed) record only.
I have been trying to use a hyperlink to a macro that uses "OpenForm," form name "ViewCustomers," but I don't know how to filter it down to just the current record.
I hope any of this makes sense. Any insight would be really helpful.
on a form with several subforms i have ran into some trouble. I have the subforms use code to determine if a scrollbar is needed. As a recordselector I use a listbox on the main form.
When i close the mainform, it sometimes makes a popup box appear saying that there is no current record.
Sofar I have determined the following :
- Removing the code from the form, removes the error.
- The code is only executed when selected records in the listbox have related record(s)
- The error only occurs when the code is executed at least once and the last selected record has no related record(s)
I dont know if altering the code would have any success, cause it won't run anyway when I select the last record (with no related records).
I guess moving the code to another section would be better, but I've tried other options like 'on focus' etc, but could not find one that responded.
HELP PLZ :)
Private Sub Form_Current() On Error GoTo Error
'kijken of verticale scrollbar nodig is maxRegels = 14
Dim rstCount As DAO.Recordset Set rstCount = Me.Recordset If rstCount.RecordCount > maxRegels Then Me.Form.ScrollBars = 2 Else Me.Form.ScrollBars = 0 End If
Error: If Err.Number <> "0" Then Else MsgBox Err.Number & " " & Err.Description End If Exit Sub
End Sub
ps. I've tried to catch err.Number = "3021" in the Error: section, but without success
I'm really new at this Access database, but I've managed to create a database, tables, forms, macros, report and a switchboard, most of which work. Printing the current form command button works great.
I really need to email the current form or record, but can't seem to figure it out. I can email the report using the snapshot format, which is what I'm after, but it includes every record. If I email the form, the format has to be chosen and it doesn't look like the report.
Can someone help me with this? Thanks so much, Jolene