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 RepliesI have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
View 1 Replies View Related 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
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.
How I would go about appending data to a table that has been entered by a user on a form.
My initial idea to tackle this was to create a number of text boxes as a method of user input which would all then transfer to a table but this doesn't have much longevity to it(if the database requires additional columns to be added, etc.)
The setup at the moment is two tables, a main table, and a temporary table(which is where I intend to first store the user input, this is so that the user can view what they have entered and make any necessary changes(undo))
I'm wondering if there are any easier ways to go about this such as, a msgbox appears and asks the user to enter each individual column data for a row. This doesn't sound like the most efficient way but efficiency isn't a priority right now and is something I'll look at later.
I'd like it so that a user can input data into a number of text boxes, a button has an onclick event that will append all entered values to the temporary table, this is then relayed(I'm assuming through requery?) back to the user as a way of checking before really adding it to the main database, and then if they're happy there is another button which is then enabled so they can add it to the main database.
I have a form in Access2000 with 5 text fields which get transferred to the table for each new record. Is there a way i can "LOCK" one field so that once the user has input that data it never changes until closed. I can already lock the field but once i create a new record the field then goes blank.
View 4 Replies View RelatedI have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number
Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Honor777
6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus
5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Dishonor777
6Honor9999
7Honor6666
8Honor7777
9Honor666
I managed to count the performance of users. The only thing I need to do is to put the names of the users on the form (under different tab) so I can select them and they show their performance. The best would that they are showing up there in the form till I change the names. I use now a text field to write the name, but when I close the database and open, I need to add the name again.
If I could use somehow the names from the users stored in a table, that would be great.
how to stop user from leaving field without selecting item or entering data
View 2 Replies View RelatedI made it by using "query", but now I forget it how I make the relation on this situation. The Sample file is attached.Here is my problem.
I have 3 Table on mdb file, named Table: A, B, Status
Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number
Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl number
status
amount
1
Honor
5222
2
Honor
855
3
Honor
988
[code]...
I have to two tables and I was add data to second table only if the field matches. Lets I have two fields site and recid on both and I want to update site on the second table only if recid from the first table matches the recid from the second table.
View 1 Replies View RelatedHow 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.
I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:
Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")
[Code]....
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
View 3 Replies View RelatedI've read over and over that calculated data is bad, that's fine with me... but here is my dillema...
I have four fields that interact with my calculation in a single form. There is only one table in the database.
AmountF (The stored data in the table) (amount financed)
FC (calculated by =[amountf]*.25) (finance charge)
Amount Paid (used in calculation of total due)
total due (calculated by =[amountf]+[fc]-[amount paid])
The math already works, everything works. I can choose to print the form and it looks great. However, I would like for it to (when I'm done printing the form) somehow save the total due data to the AmountF column in the table. AmountF is in the same line as all the other information, so I would like it to not create a new record just update the old one.
All four of these fields have a text box in the form for themselves.
So, I have a form [frmmembers] which will enable the table [tblmembers] to be updated when a button is pressed. When the button is pressed, it will enable the fields "First Name", "Last Name", and "Password" to be updated.
At the moment, the data changes without pressing the button (as soon as it is entered into the text boxes on the form), which means that a password could be accidentally changed and could prevent the user being able to enter the system (which requires their personal MemberID and Password).
Is there a way of making it so that the data inserted into the text boxes on the form will only change the data in the table after the update button is pressed?
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.
We have been trying to copy data from a combo box to a field without any success.
===
Private Sub PortfolioCode_AfterUpdate()
' From the combo box "PortfolioCodek" Place the field data into Column # 1
Me.PortfolioCode = Me.Broker.Column(1)
End Sub
===
I am working on a shared database. There are two tables in the database.Table1 have all the employee personal information like employee ID, name, nationality, date of birth etc whereas Table2 have the salary information like Basic, HRA etc. The two tables are joined by Employee_ID field.
I have created two forms, Form1 is only based on Table1 whereas Form2 have fields from both Table1 & Table2. The issue is that if I use to enter the data using Form1, the employee_ID is not automatically updated in table2. On the other side If i use to enter a data by using Form2 then everything is working fine. Is there a way to update a field value which is not in form?
I have two combo boxes that contain data from one table.
table has two fields: Name and ID
cboName
cboID
I would like both combo boxes to update each other.
Example if start typing in the cboName box it fills in after update I would like the cboID to be updated with the correct value and vise versa if i start typing the ID in the cboID box when selected the cboName should be updated.
example table
ID NAME
1 joe
2 jane
3 mark
So if i type in or select 1 in the ID combo box it should put 'joe' in the Name combo box.
Or if i type in or select jane in the NAME combo box it should put '2' in the ID box.
I have read how to cascade combo boxes but that is not what i think i need.
I am using a Access 2010 web database.
My intentions are to get my combo box to display data into a field in my form, when it is changed/selection is made. So far my form has the following:
Combobox: CBOCharacters
Field: CharacterGender
Both of these are in the same table; tbl_Characters. The data type for CBOCharacters is a number and its called CharacterID in the table.
So far I have created this piece of code:
Private Sub CBOCharacters_AfterUpdate()
Me.CharacterGender = DLookup("CharacterGender", "qry_StillNeeded", "[CharacterID]= " & Me.CBOCharacters)
End Sub
When I run this event procedure i get an error:
Runtime error '2471'
The expression you entered as a query parameter produced this error '[CharacterID]'
I have a split DB accessed and updated by several Users. How can I limit the update of one table to only one User, administrator if you will. :confused:
View 3 Replies View RelatedI am adding a combo box to a form with data sourced from a table. Everything seems ok but when I select an option from the combo list and move to the next record the selection is repeated there also. How do I get to select a different option in the next record?
View 1 Replies View RelatedI have a form with subform (datasheet) .
On my subform i have one field which shows the
productId ( which is filtered from combo boxes on main form) .
productId contains two columns
1 bound column(id) ( hidden with width 0 )
2 column (desription) ( shown with width 2 )
Now after entering the data when i come back to the form again . It hides the data from the productId field on subform , because data in combo box (on main form) doesn't match. Although the row is showing up , only the text in field (productId) is hidden.
Now when i change data in combo box on main form to match the ProductId , then the productId field text shows again. after changing combo box it hides . What i need is the existing data should not hide when i change the combo box selection.
see pic 1 where product matches and pic 2 where not. please note i am using master child relationship on the form/subform.