Forms :: Update Table That Is Recordsource For Combo Box That Is Updating That Table

Mar 29, 2013

I have a combo box (cboManifestNumber) that is based on the following table:

tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)

This table is related to:

tblTSDF
TsdfIDPK (autonumber PK)

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.

View Replies


ADVERTISEMENT

Forms :: Updating Table From Cascading Combo Box(s) Fox?

Mar 25, 2013

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

PlanktonAnalysisIDWaterbodyName172
311411511677118397104113
tblWaterbodyWaterbodyIDWaterbodyName2Ashland Pond3Bantam Lake4Crystal Lake5Gardner Lake6Hungerford Park Pond7Lake Lillinonah8Lower Bolton Lake9Middle Bolton Lake10Pocotopaug Lake11West Thompson Reservoir

View 4 Replies View Related

Forms :: Prevent Combo Box From Updating Table

May 21, 2015

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.

View 2 Replies View Related

Queries :: Update Query (table To Table) Not Updating All Records

Nov 26, 2013

I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)

Here is my SQL :

Quote:

UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];

I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)

If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.

However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.

I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.

View 3 Replies View Related

Modules & VBA :: Updating Table Where Table Name Depends On Combo Box Value

Jan 17, 2014

I am trying to update a table with the value of a text box on the form where the table to update is as selected from a combo box on the form.I keep getting the following

Error message:
Run-time error 2465
Microsoft Access cant find the field & table_to_update & referred to in your expression..

But really can't see what I've done wrong. Have checked that the table_to_update string does contain the name of the table so guess it must be sql..

Code:
Private Sub Command91_Click()
Dim table_to_update, sql_string As String
table_to_update = Me.Combo49
Debug.Print table_to_update
sql_string = "UPDATE [" & table_to_update & "] SET [" & table_to_update & "].[Project] = """ & Text89.Value & """ WHERE [" & table_to_update & "].[ID] = " & Forms![T_entity]![" & table_to_update & "]![ID] & ""
db.Execute sql_string
End Sub

View 1 Replies View Related

Forms :: Update Table If User Add New Data In Combo Box Field

Jan 9, 2014

I have a combo box that get its values from another table the problem i am having is when a user don't see the info they have in the combo box then enter the new data into the combo box field but it don't update my table with the entered values. How can i fix this to update my table if the user add new data in the combo box field.

View 3 Replies View Related

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.

View 1 Replies View Related

Update Query Not Updating Records In Table

Nov 3, 2005

I created an update query that says it has successfully updated 600 records, but when you go into the table, it has not been updated. I am working with an .mde. Does that make a difference?

I first created a select query to find everyone in TX. Then I changed it to an update query to change all TX to CA. Since it did not update the table, I tried running the update query again and this time it says there are not records to update (since it uppposedly updated my 600 records).

Your help is appreciated.

View 1 Replies View Related

Forms :: Combo Box Recordsource Requery

Jan 7, 2014

I have a recordclone combo box for navigating between 240 company records, so the form will go to the correct record on updating the combo. Instead of clicking on the down button and scrolling through the list, I would like to enter letters into the box and update the record source automatically as I enter them.

I'm not sure what events to use since there appears to be very subtle differences between them (eg between 'on dirty', 'on change' and 'before update').So when I type in 'T', only those companies that start with 'T' are viewed in the combo box.I have discovered the VBA 'dropdown' command which would be useful for the user.

Code:

company.dropdown
Table_Company
Company_ID: autonumber
Company_name: text

The name of the form is "form1" and the combo box name is "company".The record source for the combo box is "query_company". In the form the ID field is hidden and the company name is not hidden.The criteria I have for the combo would be something like this.

Code:

SELECT Table_Company.Company_ID, Table_Company.Company_name
FROM Table_Company
WHERE (((Table_Company.Company_name) Like [forms!]![form1]![company] & "*"));

View 3 Replies View Related

Updating Table Data Using A Combo Box

Sep 4, 2006

Hi all
I have been nutting out this problem but have been unable to find a solution, even my learned colleague is at a loss to help. This is an data update query using combo boxes and forms.
I have 2 databases, Data and App, I have linked 2 tables, Main and Supervisor from the Data.mdb to the App.mdb. Supervisor has 2 fields ID and Name. Main has multiple fields but is linked to Supervisor by the ID field. I have a query that gets details from the Main Table and this is entered into a form. I deleted the SupervisorID text box and inserted a combo box using the wizard, it gets its data from a query that gets details from the Supervisor table showing the Supervisor name, the ID field in the dropdown is hidden. The combo box selection is held in the SupervisorID of the Main table.
What I want to do is change the Supervisor name using the combo box however I am unable to select another name from the dropdown list.
I have tried changing the Data Entry property of the form to Yes, this did not work. Allow edits is set to yes. I have tried adding another combo box which gets the data straight from the Supervisor table but I have the same problem.
Can anyone help, we think it is a simple property setting but all we have tried has failed to date. Thanks in advance.
Craig

View 4 Replies View Related

Updating Table From A Combo Box Entry

Oct 22, 2007

I am learning access on my own, so please bear with me.
I am using Access 2000.
I have a form with a combo box. I use this form to enter data into the database. The combo box selections are from a separate table. If the required entry is not in the drop down menu selections from the table, the user needs to type in the proper selection. If this happens I want the table driving the combo box dropdown to be automatically updated with the new entry so that the data will appear in the drop down menu selections the next time. How is the best way of accomplishing this. Thanks for any answers and examples.

View 4 Replies View Related

Forms :: Recordsource Does Not Exist - Combo Box Value Is Null

Mar 14, 2013

I am assigning a recordset using vbs code. The variable "CB_proj" is a combobox value. The problem comes when the combobox is null, the program errors out and even if I try to exit the routine with an if statement, it still compiles the line of code and errors out because the object doesn't exist. how I can get it to not compile that error if my combo box value is null?

CB_proj = Forms!CC_MAIN.CB_proj
strSQL = "SELECT " & [CB_proj] & ".* FROM " & [CB_proj] & ";"
Forms!CC_MAIN.projdat.Form.RecordSource = strSQL
Forms!CC_MAIN.projdat.Form.Requery
Forms!CC_MAIN.Form.Requery

View 3 Replies View Related

Combo Box Not Updating Table Field Properly

Aug 16, 2005

I have created a combo box with the two fields CompanyID and CompanyName. I used a query for my combo box and it looks at my company info table for the info. I want it to update the two same fields on my contact Table but it updates the company name field with the CompanyID and and the company ID field dosent update at all. I am not sure were I went wrong please help.

Rickilynn

View 3 Replies View Related

Updating Relational Table From Form Using Combo-boxes

Apr 15, 2005

Hi,

I have two tables, tblCountry and tblLocation. With the following structure

tblCountry
ID
Name
Text

tblLocation
ID
Country_fk
Name
Text

As you can guess tblCountry lists all of the countries, tblLocation lists all of the locations in each country, the tblLocation.Country_fk field is linked to tblCountry.ID.

I want the user to be able to edit [tblLocation].[Text] using a form. They simply select the country and then the location using combo-boxes and then add or edit the content using a textbox.

In my form I have a combo-box that displays the country names, the RowSource is set to
SELECT [tblCountry].[ID], [tblCountry].[Name], FROM tblCountry ORDER BY [Name];

I then have a second combo-box that lists the locations for the selected country. This uses an AfterUpdate() procedure to select [tblLocation].[ID] using an SQL query based on the value of the country combo-box. I.e:

SELECT [tblLocation].[ID],[tblLocation].[Name],[tblLocation].[Text] FROM tblLocation WHERE [Country_fk] = " & Me.country_box.Value & " Order By [Name]"


I want to be able to have a textbox that then displays [tblLocation].[Text] for the selected location. Thats where the problem arises. I can't find a way that will let me display any content thats available for the selected location AND let me edit it. I've tried using UpdateAfter procedures, different bindings (tables, queries based on the value of location combo-box).

Can anyone suggest how I can display [tblLocation].[Text] based on the value of the selected country/location and be able to update the information via a textbox?

Any help would be appreciated!!!

Thanks

Jon

View 3 Replies View Related

Updating Combo Box Defaults With Optionbox And/or Check Box For A Table

Feb 28, 2006

Hi All,

I am wondering if it is possible using VBA to update using either an option group or check boxes as shown in the frmDefaultValue in the attached file to update two tables tblLabelNumber and tblMediaType.

The form frmMedia contains two combo boxes. I am trying to use the form frmDefaultValue with an option group and check boxes as shown as not sure what is the best method here to update the values in the two table tblLabelNumber & tblMediaType at the push of a button Update as in the form?

Look forward to any comments on this.

Robert88

View 8 Replies View Related

Forms :: Update Data From One Table To Another Table Using Form - Access 2010

Dec 16, 2013

How to update data from one table to another table using form.

I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.

View 1 Replies View Related

Forms :: Update Table From Text Box Already Linked To Another Table

Dec 13, 2013

I have a form that contains the following: Combobox, (Lists BadgeNum from tblPersonnel)

2 Textboxes (LastName, FirstName) populated via code from the combobox using info from the same tblPersonnel.
2 labels (one containing Date, another containing Time)
ToggleOnButton (Valueof 1)
ToggleOffButton (Value of 0)
Savebutton

This form is basically used as a cheap police timeclock. All Im trying to do is when a user chooses their name from the combobox, clicks ON or OFF and then save, is just write the much of the same info to a table. Specifically, BadgeNum, DateIn, TimeIn, DateOut, TimeOut.

View 4 Replies View Related

General :: Update A Field In One Table From Another Table Via Forms

Apr 26, 2013

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.

View 2 Replies View Related

Dates Not Updating Subform RecordSource

Jul 25, 2006

Hello,

I have a dropdown menu which has a lisrt of dates (mmm yyyy), and when one is selected it updates 2 boxes with 01/mm/yyyy and 30 or 31/mmm/yyyy

Private Sub cmbDate_AfterUpdate()
Me.frmData.Form.RecordSource = "SELECT * FROM qry_NatxReg WHERE [Company Name]='" & cmbCompanyName & "' AND [Time of Attempt 1] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#"
Me.frmData.Form.Requery
End Sub

When i choose June, it works fine and gets all the june dates from [Time Attempt 1]

But when i Choose July, it includes June.

I only have data for June and July as it's still in testing.
I can't wrap my head round it!!

View 12 Replies View Related

Forms :: Add Records To One Table And Update In Another Table

May 16, 2014

I have three tables: Members, Promotions, Ranks.

Members has all the data specific to a member and contains just one record per member.
Promotions contains information on member promotions and contains multiple records per member.
Ranks is a table of ranks and data specific to each rank such as name, description, title, fee.

I have a Promotion Detail form used for adding new promotions. I use some VBA that auto completes other fields on the form with information from the Ranks table based on the rank selected. The user has the ability to modify any of the data.

What I would like to do is update the Member's title (in the Members table) when a new promotion is added to the Promotions table. Their title is determined by the information from their latest promotion.

I'm trying to automate as many processes as possible.

View 5 Replies View Related

Combo Boxes To Update Table

Mar 1, 2006

Hi all, I have 2 tables, “DATA-CUSTOMER” and “DATA-MAIN”“DATA-CUSTOMER” has the following fields:Rep_code, Area_code, Name, Update (yes/no)“DATA-MAIN” has the following fields:Rep_code, Area_code, NameI have a form which has 3 combo boxes, looking at the following fields: rep code, area code, name,. These combo boxes are picking the data up from querys which look at the table: “DATA-CUSTOMER”I am trying to enable the user to select from the 3 combo boxes these 3 fields and it should update the table “DATA_MAIN” with these details. It appears to update the "DATA-MAIN" with one record but keeps overwritting the first record, and the combo fields in the form already have fields selected, i would like them to start of blank when entering the form.Any help would be appreciated, attached is test database that I cant get to work properly.cheers, Kevin

View 4 Replies View Related

Show Only One Instance In Listbox With Multi-table Recordsource

Oct 9, 2005

I've been trying to determine what recordsource to apply to a listbox to get the following results for its three columns:

Column1 Column2 Column3
AddressID Address Owner

The data is being pulled from two tables:

tblAddress
AddressID (pk)
HouseNum
Street
Apt

tblOwner
OwnerID (pk)
AddressID (fk)
LastName
FirstName
Current

Problem: I need each record from the Address Table to be shown with the most current Owner information from the related table. The most current Owner Information record is identified by a checkmark in the 'Current' field.

I've tried the following queries:

This query gives me duplicate addresses for each instance of owners:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID;

This query gives me only addresses that have a current owner, the problem is, sometimes a house won't have any related owner record in the owner table:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID WHERE tblOwner.Current = True;

Can this even be done with a query? This is driving me nuts. Help!

Scott

View 1 Replies View Related

General :: Updating Table With Forms

May 13, 2013

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.

View 12 Replies View Related

Forms :: SQL With Textbox - Updating Table

Sep 23, 2014

I have an sql which is used to update a table

I am wanting to update the column1 with the value of an unbound texbox on a form

View 4 Replies View Related

Update Field In A Table Using Combo Boxes

May 4, 2005

Hello,

I am a basic/intermediate user of Access. I have been given a small assigment to
update a field (the primary id field to be exact) in one of my tables based on the choice
selected on my combo box on my form.

Tables:
Survey_Answer (N/A, yes, no, Dev) with sur_ans_id being the primary id. (this is the
table that I am using to populate or pull information in my combo box. sur_answer: The NA, yes, no and dev.

tblSurvey: has foreign key sur_ans_id.

Therefore, when the user selects yes, no etc. from the combo box , it is supposed to pull the id (1 or
2 or 3 or 4) based on the choice and update table Survey's sur_ans_id.

Currently when the choice is made, it duplicates values (even though i have for my row source for the
combo box: SELECT DISTINCT sur_answer FROM Survey_Answer in the Survey_Answer Table.

My Main code to update I have put in On change under the combo box property is:

Public Sub DoSQL()

Set dbs = CurrentDb
Dim strSQL As String

strSQL = "UPDATE Survey " & _
"SET Survey.sur_ans_id = (SELECT Survey_Answer.sur_ans_id FROM Survey_Answer WHERE cbosur_answer.Column(0) = Survey_Answer.sur_answer)" & _
"WHERE Survey_Answer.sur_ans_id = Survey.sur_ans_id AND Survey.que_id = Question.que_id"
DoCmd.RunSQL strSQL
End Sub

However, this is not working: I know it has something to do with my BOUND COLUMN count property but that is mostly where I am stuck. I cannot
update Survey.sur_ans_id properly. I understand the concept of BOUND COLUMN but i don't know how to apply it to my SQL statement.

I am sorry for the long narration but any help or guidance will be greatly appreciated.

Thank you

View 9 Replies View Related

Forms :: Updating Table From Unbound Textboxes

Aug 28, 2014

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved