I have created a form and subform using form wizard where users could enter the data of a new order.In the form, there are some expressions where it does a computation of the fees that the company earned for each order. This is a percentage of the gross income.The subform expression formula updates the fee amount and net income automatically when the gross income is entered and fee percentage entered.
Is there a way to update the fee amount and net income which the expressions derived into a data field in a certain table?I actually need these information to be in the table too, as data as they are required by other users.Or is there a better way to do it?I am mainly using wizards and don't know how to use any SQL or VBA.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
updating my table when I use cascading combo boxes in my form.What is happening is that my table is being populated by the xxxxID column vice from the xxxxName column that is being used from that specific table.
here is my visual basic code that I am using to determine what the subsequent combo box will display.
Option Compare Database Option Explicit Private Sub cboPlanktonID_AfterUpdate() ' Set the Family combo box to be limited by the selected Plankton Type Me.cboFamilyID.RowSource = "SELECT tblFamily.FamilyID, tblFamily.FamilyName FROM tblFamily " & _ " WHERE OrderID = " & Nz(Me.cboPlanktonID) & _ " ORDER BY FamilyName"
[code]...
Example of the Combo box Row Source is: SELECT [tblWaterbody].[WaterbodyID], [tblWaterbody].[WaterbodyName] FROM tblWaterbody;
My Control Source is PlanktonAnalysis.WaterbodyName
When I fill in the form with the data, The Waterbody name is visable for selection (example: I see "Lake Lillinonah" in the cascading combo box, But when I save the record in the PlanktonAnalysis Table I get a number in the WaterbodyName column vice the name of the waterbody
When a user selects a name from a combo box then 11 textboxes are populated with personal information.
What I am looking for is when the user edits the persons information (i.e. changes the persons phone number to a different number), how do I update the table with this information?
Is there a way to only update fields that have changed? or do I have to save all textboxes?
I have read about an "Update Query" and a SQL Update, but I did not think the two applied. I am sure that I am wrong, lol.
I tried the "Docmd.Runcommand acCmdSave (in the OnClick event cmd button) but it did not make any changes to the table.
This is what I put in the OnClick event:
Code: Private Sub cmdSaveEdit_Click() DoCmd.RunCommand acCmdSave TextBoxLockDown Me.Requery End Sub
I am trying to make an amend record form which gets values from a subform. Most of the values that I need to amend on the form do amend. My problem is that I need to amend a value that is also used to find the record.
The value in the Table(BookInTable) that I am trying to amend is Barcode which is a text value. I have been trying to pass the initial value into a string(Bar) and have been using an SQL Update string.
Code:
Private Sub Command23_Click() Dim Bar As String Bar = Forms!FrmAmendOrder.AmendOrderSubform!BarCode If IsNull(Me![POTxt]) Or (Me![POTxt]) = "" Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me![POTxt].SetFocus
[Code] .....
BarTxt and POTxt are textboxes that show the initial values and BarTxt is editable.
By adding watches I can see that when I click the button Command23 Bar = the initial value and BarTxt.value = the edited value. But when I look at the table nothing has changed.
I have a combo box on my form which loads fields from a table and displays them using
Code: SELECT DISTINCT table_team.team FROM table_team;
I then use
Code: =[qry_showteamforedit]![team]
in the default value for the combobox to show the team which is saved in the current record.This is the qry_showteamforedit:
Code: SELECT table_team.team FROM table_team RIGHT JOIN table_staff_details ON table_team.ID = table_staff_details.team WHERE table_team.ID = table_staff_details.team;
My problem is when I move through the records, if I change the selected value using the combo box it changes the actual value in the table from the one that was selected to the new one. If I was on record 1 and the teamid saved in there was 1 . It would display "team one" but if I changed that to "team two" it would change record one to say "team two" instead of "team one".I have been searching and found that this is because it is bound to the table so need to remove the text from Control Source, which when I do, breaks it, and it doesn't display the saved team.
what I would like it to do is display all the teams, but default to the one saved by using the id saved in the main table, but allow me to change this value. I would also like a second cascading combo box which will display a list of subteams dependent on what main team was selected and again, default to the values saved in the main table. I have managed to get cascading combo boxes working but combining them with my tables and queries is proving difficult. This is how my tables would be ( just showing the relevant fields)
Staff_table ID Name teamID 1 Dave 1 2 Tom 1 3 Matt 2
team_table ID team subteam 1 team1 subteam1 2 team1 subteam2 3 team1 subteam3 4 team2 subteam4
Is it is the subteams that will be unique I would like to save the subteam ID to the teamID field of the staff_table. that way i can retrieve the team and the subteam using the same ID.
I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number Dispensed Date- date with dd/mm/yyyy format Dispensed Type- text Quantity- no. of days Next Collection Date- date with dd/mm/yyyy format
The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.
Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.
And I can display any existing question and make updates to the question and answer, that works fine. BUT, what I want to do is update which category a question is in. If I change the Category in the form, it updates the 'Category' in the Category table. What I want it to do is update the CategoryID in the question table.
i am getting stuck while updating the data in the database table using a command button in the MS Access2003 forms. when i click the command button in the form, a message "Run time 2185: you cant refer to a property or method for a control unless the control has a focus". the code is as follows.
rivate Sub Command10_Click() Dim query As String
query = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"
If (cn.State <> 1) Then cn.Open "dsn=ABC", "", "" End If rs.Open query, cn, adOpenKeyset, adLockOptimistic RESOURCEINFO.SetFocus rs.Fields(0) = RESOURCEINFO rs.Update
rs.Close Set rs = Nothing cn.Close Set cn = Nothing On Error GoTo Err_Command10_Click
I have a form that contains a combo box (cboEmployeeName) that pulls data from a query and populates three text boxes (Work Area, Last Name, First Name), This part works fine. Because the text boxes are being populated by the Combo box, they are not bound to the record source tblTrainingSchedule). I need the info that is in the text boxes to populate the respective fields in the record source.
I tested by adding "=tblTrainingSchedule!WorkArea=[cboEmployeeName].Column(3)" (column 3 is the work area) to the "after update" control but it does not populate the data.
I need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.
The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.
I am having a problem updating a record on a table called BookInTable . The field I want to update is called Engineer and the record is found using the field Barcode. I have a form with 2 text boxes BarTxt and EngTxt as well as a button called SaveBtn. on the OnClick event of the SaveBtn I put the following code
Code:
Private Sub SaveBtn_Click() If IsNull(Me![BarTxt]) Or (Me![BarTxt]) = "" Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me![BarTxt].SetFocus Exit Sub End If
DoCmd.RunSQL "Update BookInTable SET Engineer = " & Me!EngTxt & " WHERE BarCode ='" & Me![BarTxt] & "'"
End Sub
But this is bringing up the enter Parameter value dialog box for whatever I type into EngTxt. If I enter text into that box and then click okay it then updates to the record. how can I stop the from enter Parameter value dialog box appearing?
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded 121/2/201310:00:00 AM3:00:00 PM 221/3/20132:00:00 AM11:00:00 AM 321/4/201312:15:00 AM11:30:00 AM 421/5/201310:25:00 PM11:00:00 AM 531/2/201311:00:00 AM3:30:00 PM 631/3/201312:00:00 PM10:00:00 PM 731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
I'm trying to update one table's field, via a Form, with certain data from another existing table in my DB when I enter key data in this first form. Example:
Table Equipment ... Some columns... Year, Make, Model, LIcPlate, etc.
Table Fuel ... Some columns... Year, Make, Model, LicPlate, Fuel Dispensed, Milage, etc.
Form for Fuel has Year, Make, Model, LicPlate, Fuel Dispensed, Milage, etc. BTW, it will take Year, Make and Model to fully qualify the search/lookup as there may be more than one occurrence of a Year and Make in the Equipment table, so Model is necessary to fully qualify. . Yes, something like VIN would be a simpler lookup but remembering a VIN is much harder than entering a Year, Make and Model.
In a Form over Table Fuel, I want to have the LicPlate field (possibly other fields as well) automatically updated from Equipment Table when I enter the Year, Make and Model in that form.
I'm assuming the solution involves creating VB code, of some such, via an Event (AfterUpdate) or some such built through the LicPlate field in the Fuel form. A mass Update via SQL is not appropriate.
I am currently modifying a form on my access database. More specifically the on click event of a button on the form. The form is based on a Table that only has 3 fiels, ID, GoingToCal, and Location. ID is the autonumber. GoingToCal is a multiselect list box on my form. Location is just where the user is submitting the entry from. The table is simply a way to track what went out at what facility.
The issue that I am having is 2 part. The first error I have doesn't create a runtime error, it simply doens't work. What I am attempting to do is update a recordset of the items selected in the list box. The items are in another table, and I have based my recordset on a query. When I run my code I am expecting first for the specific record to be found(I think works), then update a yes no box to yes. The Yes No field is called StillOut. It is a part of the query. However it doesn't update the Yes No box to Yes, nor does it seem to update any of the boxes. So I am confused as to what I am doing wrong that the field for that record isn't updating.
My Second issue, which I know there can be a few possible ways to work around I just can't make some of them happen. I am using the FindFirst for how I narrow down my recordset to just the one record I want to update. It doesn't seem to like "," with it. I have tried many times when setting the OpenRecordset to the specific record using SQL and a mix of SQL and the Query but no matter how hard I try I can never return any results. It constantly says too few parameters. That is why I am using the FindFirst command.
how to make the record update the Yes No box and how to specifically change just the records that have been selected in the form and a way to select those records.
If Me.GoingToCal.ItemsSelected.Count <> 0 Then For Each oItem In Me.GoingToCal.ItemsSelected If iCount = 0 Then
I have a database application that i intend to split with the table part on a server and the forms part on each users desktop. It will be used by approx 10 users. While the probability of different users accessing and updating the same form is small, the possibility exists.
Is there a way i can prevent any weird multiple update stuff happening? I'm using Access 2003
i have developed an access database and working on interface through forms & reports .i needed that a user must have to click save button to update the data and i have found that without an unbound control its hard to achieve functionality .so i want to know step by step how to update records using unbound controls and a save button .
I have second table called Steps (tblSteps) that is related to tblTask via the field "StepNumber" which is a concatenation of [tskID] & "." & [StepOrder] So if we look at washing dishes as a task then the steps are as follows:
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink , 1, 12.1 512 Turn on tap, 2, 12.2 205 Put in detergent, 3, 12.3 210 Place dishes in sink, 4, 12.4 435 Turn off tap, 5, 12.5 etc
If I decide delete the third step I get the following:-
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink, 1, 12.1 512 Turn on tap, 2, 12.2 210 Place dishes in sink, 4, 12.4 435 Turn off tap, 5, 12.5
Is there a simple way renumbering the StepOrder entries when one step is totally removed to give me the following?
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink, 1, 12.1 512 Turn on tap, 2, 12.2 210 Place dishes in sink, 3, 12.3 435 Turn off tap, 4, 12.4
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code: SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next FROM calls WHERE (((calls.firm_id)=[firms].[id])) ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
My form is setup with Continuous view, with data feeding from a table. The structure is straighforward (excel style). AgentID, workHrs, holidayPaid, holidayUnpaid, sickHrs etc. The structure is not normalized, and I know the risk. This is basically only for human interpretation, not really a structure that will cause instability or patched up working of the DB.
I have the continuous form view, so it can be viewed in one single display, I have placed a Totals at the footer, which is populated by nothing but a simple = Sum(columnName). This works great in most of the computers, except one. My boss's computer. Which is where it actually needs to work. I am baffled, because I have tested it on almost every single computer in my office, everyone's work. Just his computer is playing up. I am unable to pin point the problem. The status bar keeps going "Calculating..." but even after giving it a solid 5 minute it does not seem to work !