Forms :: Updating Fields Via Code Not Updating Table
Dec 16, 2014
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 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 have two tables, one is a list of customer details including email addresses and the other a list of customers and email addresses that have unsubscribed from my mailing list. How can I use the unsubcribe table to update a field in the details table so I know who's unsubscribed?
I have a form where there are four fields that the user selects via a drop down combo box: account number, Group, Name and Date. The entire form is linked to a table named 'Questionaire', but all of the drop down selections are sourced throw other tables and queries. At the end of the day, all of the information flows to the 'Questionaire' table, which is the DB's main table. Subsequently, there is also a subform as part of this form, but that is beyond the question here.
What I am trying to do is change my Group field to be something that automatically populated by the database and not something that the user needs to identify. For instance, when the user begins to enter his or her information into the DB, they input their name, date, and account number....once the account number is entered, i would like to have the group automatically generate and NOT be something they must choose.
The possible account number selections are driven from a different table named 'Accounts', and in this table are the fields: Decription, Group, and so forth.
What do I need to code or build in order to have the 'Group' ID automatically update when the 'Account' number is entered?
I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
Hello everybody. I am new to Access and as such I don’t know how to perform the following. Please help me out.
The project is about a library loan system. I have a database consisting of the following tables: member (MemCode, LName, …), book (BkCode, Title, TotalCopies,…), purchase (PurDate, BkCode, CopiesBought,…) and transaction (MemCode, BkCode, RentalDate,ReturnDate,Returned).
TotalCopies is the total no. of copies there are of the book CopiesBought is the number of copies that have been purchased RentalDate is the date on which book is being loaned ReturnDate is the last date on which the book has to be returned Returned is a Yes/No field to indicate if the book has been returned
In the library a member can take only one book at a time.
Problem 1: The library may have several copies of a particular book, but all will have the same code. For example, a book, let’s say Harry Potter 6 has code 97. Initially only 1 copy of the book is purchased. When the librarian sees the book has great demand, he decides to purchase 3 more copies. All the copies will have the same code, i.e., 97. Only the TotalCopies will become 4.
BkCode is Autonumber in book table and Number in Purchase table. Relationship is 1:many.
I have a Purchase form to record details of books being purchased. I have a command button “Book form” that opens the book form when the user clicks on it. He will use it if the book being purchased is not already available in the library. What I want is:
(i)If he opens the book form to fill in details of the book, then when he closes the form I want the book details to automatically appear in the corresponding fields on the Purchase form. (ii)The CopiesBought will have to be used to update the TotalCopies in the book table. How should I do this?
Problem 2: When the user wants to record a loan, the system will need to check if that member already has a book which he has not yet returned. If it is so, then the system will need to provide a prompt to inform the user of this and consequently blocks the user from completing the transaction. How do I do this?
I have two forms linked by a field "JUVIS". My problem is that I can't get the second form to show data newly entered on the first form without moving to another record on the first form. I have a command button on the first form that opens the second form. All works well, meaning the forms are synchronized,based on the JUVIS field, except in the case of entering new data. In this case the second form opens to a blank screen instead of showing the JUVIS field. The forms are both linked to tables not to queries. Any help would be appreciated
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
I've been working on this problme for several weeks, thinking about it for the last month or so, and am at a loss of what to try next. I'm working on a database for my office that tracks all of the permits we have issued. Every year our permit fees increase by the CPI (consumer price index) and the permit fee entry in the table needs to be updated (75 to 80 entries).
Rather than typing them in every year (too many chances for errors) I'd like to be able to update our fee schedule table (8 entries, look up table) and have that table update all of our fee entries in the permit table.
Any ideas of how to go about this? Thank you for your help.
I have a data entry form where country(ies) (there can be more than one for each project) is/are entered in a sub-form on a tab control. Countries not listed are entered by the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer) Dim strSQL As String Dim strMsg As String Dim ctl As Control Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?" If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then strSQL = "INSERT INTO tblCOUNTRY (CountryName) " strSQL = strSQL & "VALUES('" & NewData & "');" CurrentDb.Execute strSQL Response = acDataErrAdded Else ctl.Undo Response = acDataErrContinue End If End Sub
This works fine. However, if I restrict the country names by a region selection on the main form (for example if region Africa is selected on the the African countries are preselected) the NotIn List does not work. Any way to work around this?
Another question, I want the user to select a region (a combo box ) on the main form and when a new country (using the NotInList event) is entered in the subform both values go into the same record in tblCountry (which has two fields CountryName and Region). For example, I enter AFRICA for region on the main form and on the sub-form I enter Malawi which is not listed. I have tried the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer) Dim strSQL As String Dim strMsg As String Dim ctl As Control Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?" If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then strSQL = "INSERT INTO tblCOUNTRY (CountryName, Region) " strSQL = strSQL & "VALUES('" & NewData & "', FORMS!frmAddPro!Region);" CurrentDb.Execute strSQL Response = acDataErrAdded Else ctl.Undo Response = acDataErrContinue End If End Sub
But it does not work (Region is the control on the main form named frmAddPro). Any suggestions would be welcome. Thanks. Niels
I have an open form "MattersFileFrm" that has total fields on it.
I have a transaction form open where the user is entering figures into a few fields. I then want to add those figure to the total fields on the other form.
I'm still very new to Access.I have a form with subforms.Some of the fields are calculated fields.I cannot figure out what some of the fields will not update automatcally. Once I click out of the form and then back in, it will update.Tried many things like, Refresh, Requery both on the form and on the text box(es).I wish I could figure out how to attach my test database to show you.
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet. They have a common row ie say account number and other common fields that need to be updated.
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
I have this code to update my combo list when NewData is entered. NewData is not updating in the cbo box. I am new and still learning. Please help.
Private Sub POC_NotInList(NewData As String, Response As Integer) On Error GoTo cboPOC_NotInList_Err Dim intAnswer As Integer Dim strSQL As String intAnswer = MsgBox("POC " & Chr(34) & NewData & _ Chr(34) & " is not currently listed." & vbCrLf & _ "Would you like to add it to the list now?" _ , vbQuestion + vbYesNo, " POC") If intAnswer = vbYes Then strSQL = "INSERT INTO MARK20A([POC]) " & _ "VALUES ('" & NewData & "');" DoCmd.SetWarnings False DoCmd.RunSQL strSQL MsgBox "The new POC has been added to the list." _ , vbInformation, "POC " Response = acDataErrAdded Else MsgBox "Please choose a POC from the list." _ , vbInformation, " " Response = acDataErrContinue End If
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code Table: Table 1 Update To: [Table 2].[Access Bar Code]
Field: Access Box Number Table: Table 1 Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
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.
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.
Hi well baisically the title explains the dilemma... I have a combobox in which the user selects an item and this updates the subform depending on the selection. However when i make a selection i get the following error:
Rumtime error '2101':
The setting you entered isnt valid for this property.
I understand what its telling me, however i cant seemed to find within the code where i have gone wrong, so heres the code:
Private Sub Combo4_AfterUpdate() UpdateSubform
End Sub
Private Sub UpdateSubform() Dim strSQL As String Dim varWhere As Variant
varWhere = Null
strSQL = "SELECT * FROM Sub_qry_CostCentre"
If (Me.Combo4 > 0) Then varWhere = " WHERE (CostCentre = " & Me.Combo4 & " )" ' MsgBox (strSQL & varWhere)
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.
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.
