Queries :: Allow User To Pick A Record From Continuous Form And Edit It In A New Window
Apr 3, 2014
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 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 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.
A continuous form with fields Operation bounded integer, LibelBx unbounded combobox string with two columns as Sorter (Int), Phrase(str).
A table TargetTbl with field Operation(Int). A table TextArrayTbl with fields Phrase(str), Sorter(Int).
Relationship between TargetTbl and TextArrayTbl is one to many joined on Operation = Sorter.
I am trying to display the form. RecordSource = TargetTbl. LibelBx.RowSource = TextArrayTblQy.
The idea is to display every record from TargetTbl with Operation equal to Sorter from TextArrayTbl. The LibelBx combobox should display the Sorter and Phrase.
Code: TextArrayTblQy = "SELECT TargetTblQuery.Sorter, TargetTblQuery.Phrase FROM TargetTblQuery, TextArrayTbl, TargetTbl WHERE (([TargetTblQuery].[Sorter]=[TargetTbl].[Operation]));"
Code: TargetTblQuery = "SELECT TextArrayTbl.Sorter, TextArrayTbl.Phrase FROM TargetTbl, TextArrayTbl WHERE (((TextArrayTbl.Sorter)=[TargetTbl].[Operation]));"
I managed to get the TexArrayTbl displayed in LibelBx combobox, but it displays all the records whereas I want it to display only the ones with Sorter = Operation.
I have a query with a list of Customer SSNs and Claim Rep, I want to randomly pick 5 Customer SSNs for each of the Claim Rep. I can't figure out on how to do that, I was able to create an SQL code to just randomly pick the customer SSN. I don't know anything about Visual Basic codes, but if that is the only solution, please go ahead post it. Thank you very much in advanced.
I'm running MS Access 2003. My SQL editing window has a really small font size. How do I change this window's default font size? Is it in tools or options?
Help!!!!!!! I'm going blind!!!!!!:confused:
Thank you for taking the time to read this and for being willing to help out!!
I have a main form with several continuous subforms. Each subform consists of several listbox controls. I would like to require the user to select an item from the listbox before being allowed to move to the next record in the subform, and upon reaching the last record in that continuous subform, to require an entry there in order to move to the first record in the next cont. subform.
As an added bonus, it would be nice, though not necessary, to automatically jump the focus from one record to the next after data is entered. But my basic goal is to avoid skipping records.
Caveat: I cannot use the "required" option in the field to which the control is bound because that field has a default value previously entered using an append query. (The default value basically means "not yet entered" and is not one of the options in the listbox. I am using this because this field is a foreign key in the table, thus it must have a value in order to have a record with which to populate the subform.)
My apologies if this has been asked and answered elsewhere, but I've searched and, while I found a few related threads, they don't quite answer my question. Here they are for reference:
Thanks for any help! Also, if you are going to recommend VB code, could you be specific about how and where I should use it? I'm not afraid of code, but I'm definitely new to it...
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 am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?
Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.
SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005")) ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;
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 list box that fills based on the following code.....
Dim strSQL As String strSQL = "SELECT Products from [Client ProdVend] " & _ "Where Client_Account_Name = '" & Me.Client_Account_Name & "'" Me.List91.RowSource = strSQL Dim strSQL As String strSQL = "SELECT Products from [Client ProdVend] " & _ "Where Client_Account_Name = '" & Me.Client_Account_Name & "'" Me.List91.RowSource = strSQL
There is another field in the [Client ProdVend] table called ID. I want to be able to select a product in the listbox, but have that selection open up a form based on the ID field associated with that product. Right now i use this.
The Problem is if there are multiple products with the same name, instead of going to the specific instance of the product(cased on the ID)...it just opens all of the products with that name up, starting with the first one.....
I have a continuous form for which the recordsource is a query that retrieves dates from 10 days in the past to 10 days in the present. I want the record with today's date to be at the top of the form. The record with the oldest date is always on top. Is this a scrolling issue? How can I get the record with today's date to appear on top?
I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year??
Code: CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)
I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.
result = sum(cost1) - calc1 selected record value
I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.
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 am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
I have one table (500 Club) with two fields (ID) (Name) i would like to randomly pick a record from the table and display the results in two seperate text boxes on a form one for (ID) and one for (Name). I intend to use this for a monthly draw at my workplace.
I have a continuous form, which is used as a sub form in a main form. How can i put some code on a control which will work only, if the focus is on the last record (the event i would like to use is onKeyDown). I have tried using:
This is probably a stupid question and I expect the answer is no but here goes.
I have a continuous subform showing records sorted to show the latest first.
I would like the empty new record to be sitting at the top of the list rather than the bottom, so my users don't need to scroll down to enter a new record.
Is this possible, and if so what is the setting I need to use.
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 am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.
I have successfully used the following but the records returned are only the specific date choosen not that date and all prior
Like Nz(([TempVars]![tvcldate]),"*")
So I have tried the following but it doesn't work.
I have a continuous form based on a query. In the form footer I have placed a text box and a command button. One of the text boxes on the continuous form is bound to the VIN field in the query. I would like the user to be able to place the last 8 digits of a particular VIN in the text box in the footer and click the command button to bring that particular record to the top.....
In a continuous form, I know you can groupby in descending order. If you do this on a date field, then the newest record appears at the top. How do I make the blank record for entering new records appear at the top of the form instead of the bottom. Thank you.
