Update 2 Fields With 1 Combo Box
Sep 2, 2005
Hi,
Hope someone can help. What I’m trying to do is have a combo box look up values from one table and add them to another. I have used the combo box wizard to look up a value from table1 and add it to table2 but is there a way that it can lookup 2 values and add them to the second table. For instance if I had to tables like :-
Table1
First Name
Last Name
Table2
First Name
Last Name
Can a combo box lookup and update both fields for example if I have a combo looking up the first name can it also lookup the last name and add them to the corresponding fields in the second table but with the user only selecting the first name in the combo box.
I’m sorry if I don’t make much sense I’m new to access and fairly new to forums
Thanks for your help
View Replies
ADVERTISEMENT
Feb 27, 2008
I have a Combobox so the enduser can search and select items for the form below it rather than clicking the arrows for Next/Previous.However I have an issue with DLookup queries.My Combobox is called 'F_ComboBox' (I use F_ in my forms to indicate it's a form textfield and not from a table).My first field in the form, called 'F_ProdID', I want it to select the rows ID from the Combobox option selected. So it's simply:=[F_ComboBox]Which works fine, it shows the ID for what I selected... but when I do this to show my ProductName...=DLookup([ProductName], 'Products', [ProdID] = [F_ComboBox])Or the same but using the first textfield:=DLookup([ProductName], 'Products', [ProdID] = [F_ProdID])It loads once but if I select another item out of the Combobox it doesn't change, it just stays on the last result. So the query works, just doesn't refresh when a new item is picked from the ComboBox.Am I doing something wrong?Thanks,Nick.
View 1 Replies
View Related
Nov 26, 2007
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
thank you
-Tim
View 2 Replies
View Related
Aug 4, 2006
Here's the deal:
I'm an extreme newbie, I do not know access very well, nor do I know VBA, I do know PHP.
I have a for in access that has 2 user input fields, one for prodid one for shipid. I have a combo box that upon entering data into the previous 2 fields, it does a query against an MsSQL database looking for a record that has both. In any case there will only be 2 outcomes, either 1 record, or null. I would like to have that same combo box automatically use the result as it's value so that users don't have to check the box, because they won't, and so that the rest of my VBA will be able to function properly.
Can anyone assist?
Thanks.
View 2 Replies
View Related
Aug 21, 2013
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
View 6 Replies
View Related
Nov 29, 2006
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies
View Related
Aug 30, 2004
I have a combo box linked to a look-up table. There are 8 fields in the look-up table but only if one of three is chosen do I want additional combo boxes to become visible. Do I put the code in the "after update" or "on change" event of the combo box and how do I express the code-
If TechniqueCombo="caudal" or "spinal" or "epidural" then
TextNeedleType.Visible=True
(am I close??)
Thanks
View 4 Replies
View Related
Jun 20, 2005
Hi All,
I have a combo box on my form that is retrieving its list of values from a table. After I choose a value I want to write it to another table's field. I can get this to work where it is writing to the other table's field, but when I open the form to see the field of where the information is supposed to be writen to it is not refreshing. What property on the combo box should I used to ensure that the field of the other table is instaneously updated when the combo box's value is chosen.
Thanks
Greg
View 3 Replies
View Related
Jul 12, 2005
Hey All
I have a following problem - the user selects value from a combo box (cmbPrio), if there was already a value in there, new value get added, and no further action is made.
If there was no value, value is added, AND in txtDueDate date gets added (if user selected High - todays date + 5 days, if low - todays date + 10 days)
I was thinking of doing it the following way, but it doesn't work --
Value XX is not saving the data. Should it be maybe done for a different event ? And I also don't get the correct date...
Before Update
If isNull(cmbPrio) then XX = 2
After Update
If XX = 2
If cmbPriory.text = "High" then txtDueDate = Now + 5
If cmbPriory.text = "Low" then txtDueDate = Now + 10
End If
View 3 Replies
View Related
Dec 9, 2006
I have a form that displays a list of users and there programs in a sub form, I would like a combo box instead of the user ID number so that I can press on it and all the user ID's will appear, then I can select it and there details will apear. Can this be done?
View 6 Replies
View Related
Apr 21, 2006
I said I was a dummy, but here goes.
I am designing a customer information form, the form has an unbound combo box showing customer names and when clicked opens the customer record.
My problem is when I enter a new record, I have a button that saves current and opens new, but when I am in the new record, the customer I just added previously is not showing up on the unbound control, only when I close the form completely and re-open it.
Is there a code to update the unbound combo box when you click to go to new record.
View 3 Replies
View Related
Jul 17, 2006
I want to update a field (field1) in each record of a recordset (qryUpdate)
I got the following code (in green) working fine - so far so good. I actually need to update the field from a selection from an unbound combo box. This looks up a table with two fields Period ID (autonumber PK) and a text field called Quarter. I've tried various combinations including the code in red and code such as
"rst![field1] = Me.cboPeriod"
"rst![field1] = Me.cboPeriod.Column(0).value" etc
but can't get it to work. It has to be something simple but I can't see it.
Private Sub cboPeriod_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryUpdate")
Do Until rst.EOF
If IsNull(rst![field1]) Then
rst.Edit
rst![field1] = 5
rst.Update
End If
rst.MoveNext
Loop
End Sub
Private Sub cboPeriod_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryUpdate")
Do Until rst.EOF
If IsNull(rst![field1]) Then
rst.Edit
[rst![field1] = Me.cboPeriod.Column(0)
rst.Update
End If
rst.MoveNext
Loop
End Sub
View 3 Replies
View Related
May 2, 2007
Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "Uno", dbFailOnError". I have an update query which I am trying to have a combo box from a form input information, update a table with what the combo box was inputted with. (if you follow). In my query, I have one field as showing the "Update To:" filed as "Forms![Loc-1L]!Combo8". When I try to run the query, it works in the query if I manually type in a location, however when running it in the form, it gives me the error: "Too few parameters. Expected 1". Any ideas where I'm going wrong?
View 2 Replies
View Related
Jul 27, 2006
i have a form that i want to stamp with a date when it was last updated. if a change to the form is made it will be stamped with today's date. i used the BeforeUpdate event so that you could choose to save the edits or not so if you didn't mean to edit a record then the date would not change. the code is:
Dim myVar As Byte
myVar = MsgBox("Changes to this issue have been made. Would you like to save the changes?", vbYesNo + vbQuestion, "Save Changes?")
If myVar = vbYes Then
[LastUpdate].Value = Date
Else
Me.Undo
End If
my problem is that when i change the combo box values it will run this event and ask to save. i think the reason this is happening is because the comboboxes save when you change them. how do i accomplish the same task as above but allow the combo boxes to act like txt boxes?
also is there a way that when i ask if the user wants to save or not, to have a cancel button that will take no action, leaving the record open the way it was (new typing and everything) before the user tried to close the form without saving that info?
View 14 Replies
View Related
Sep 26, 2006
ok i have three tables..
Booking_Table
Booking_ID
Job_Date
Company_ID
Booker
Passenger
Company_Table
Company_ID
Company_Name
Address
Phone_No
Company_Staff_Table
Company_Staff_ID
Company_ID
Full_Name
Phone_No
each booking will only ever involve one company at a time
company 'A' will have several staff
person 'A' from company 'A' may book a job for themselves
person 'A' from company 'A' may also book a job for person 'B' from company 'A'
i have created a booking form
i have added a combo list displaying all the company names..(control="comp_combo")
i have also added a combo list displaying all staff names from all companies (control="staff_combo")
when the user selects company 'A' from the first combo.. i would like the staff combo to update and only display staff members for that company
how do i do this.
View 1 Replies
View Related
Oct 18, 2006
This is so simple but I can't seem to make it work. I've poured through about 30 posts and tried all kinds of AfterUpdate event strings to no avail. Help!
Here is the simple setup. I have one table that lists employees and their corresponding teamleader. A grouped query provides the record source for a combo box. The idea here is that you select a teamleader from the combo box and a subform updates to show all the corresponding employees. This subform is based on a simple query of all fields on the table.
combo box = cboteamleader
mainform = frmTechMaintenance
subform = frmEmployees
I've finally settled on this...
Private Sub cboteamleader_AfterUpdate()
Me!frmEmployees.Requery
End Sub
...as having the greatest chance of success based on other posts. Not sure why it doesn't work. It just doesn't update.
View 6 Replies
View Related
Jan 5, 2007
Hello,
I have been playing around to see if it is possible to operate a combo box as a one shot operation.
I.e you make a selection from the combo box then the combo box is disabled.
Only prob is that I can't figure out how to do it.
Possibly by using a message box to see if selection is correct, if Ok clicked
then data entered and comboboc.enabled = false?
Or can this not be done.
B
View 2 Replies
View Related
Dec 6, 2004
i have several fields i would like automatically updating with data from a combo box.
at the moment i can only update one. the combo box relates to a query on one table,
the fields i want updating are on a second table. is this possible?
View 1 Replies
View Related
Jan 19, 2005
Hi,
I am trying to update a subform by selecting a record in a combo-box that corresponds to a particular test when i select the said test i want an adjacent subform to change to the form displaying the test data in the subform window i have tried to use VBA to achieve this but so far have been unsucessful does anybody know how this can be done?
Regards
Gavin Cleary
View 1 Replies
View Related
Apr 4, 2005
Good day all,
I have a problem when updating a combo box on a form. I open the form from a command button on another form (No problem) when I update the combo box on the new opened form Access acts like the record set is still opened. I tried closing the original form and even the table but I still get the same problem. If I open the form without going through the button the update works. Does anyone know hoe to correct this problem?
Thanks in advance.
View 1 Replies
View Related
Apr 8, 2005
Hi All,
Thanks so much for your help in advance. I’m working on a Form1 that has a combo box with Row Source coming from a query Just like:
NAME ITEM_ID
Smith 10011
Smith 10012
Scott 10022
Clark 10015
Clark 10016
Now, I want to be able to update the Item_ID value to text boxes txt_SN1 and txt_SN2 once I click on the combo box and select the name “Smith” then show 10011 in txt_SN1 and 10012 in txt_SN2. I’m not sure if this can be done with record set or just with column values from the query.
Can someone help me with this problem? Your help will be highly appreciated.
Mosquetero
View 2 Replies
View Related
Jan 26, 2006
Hi, I am new to asp development and have been searching all through the web and books to help me but I am a little stuck and would appreciate some help please with updating a second combo box.
I am developing an online fault logging system for a school where the teachers/pupils can report a fault with any computer equipment to the technicians so they then can fix it.
I have two combo boxes called ResourceType and Resource ID which are created and populated from two different tables in an Access database (I dont want to annoy you with the code but if you like I can show you)
When ResourceType combo is selected it should automatically update the ResourceID combo from a table in the database. eg. ResourceType=Laptop then ResourceID should be populated with all the entries in the DB which correspond to Laptops, i.e. Laptop001, Laptop002 etc.
ResourceType combo is populated from table tblResourceType in the Db and ResourceID is populated from table tblResourceID. This works fine when the page is first loaded but I have problems with the onChange="handleChange(this)" function for the ResourceType combo. It is :
Code:function handleChange(nSelection){var IDSelect,iCountIDSelect = document.form.ResourceID //Reset the ResourceID combo contentsfor(i=IDSelect.length; i>0;i--){ IDSelect.options[i-1] = null} //Open Database<%set db=server.createobject("adodb.connection")db.Open"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MCB Fault Log.mdb")dim dbaseset dbase=server.createobject("adodb.recordset")%> <%Dim iCount 'counter variablesSQl = "SELECT ResourceID from tblResource"dBase.Open sSQl , db%> //Reset Counter iCount = 0<%do while not dBase.EOF%> IDSelect.options[iCount] = new Option(<%=dBase("ResourceID")%>);//********This does not work****** //IDSelect.options[iCount] = new Option(iCount);//***HERE**This works (places the count of each record in the combobox iCount = iCount + 1; <%dBase.MoveNextloop%>}
Dont worry about the SQL statement for now, I am happy just for any table to fill into the combo. Look at the //********This does not work****** line - I think I have the syntax wrong, I have looked at lots of examples on the net and I cannot find one similar to this. The line ***HERE** places the number of the count into the combo box so at least i know the the database is being accessed.
Does anyone know what is going wrong here.
Thanks in advance for any help you may give
Cush
View 1 Replies
View Related
Mar 23, 2006
I want to limit the values in a subform using a combo box. Basically I've got a Main Form that has name field etc. Selecting a different record shows me a different entry on the subform relating to a different name but now I want to use a combo box to limit what I see in the sub form another level. Any help would be wonderful.
View 1 Replies
View Related
Apr 3, 2007
--------------------------------------------------------------------------------
I have a form with two drop downs (combo box) School and Players. The first drop down I select a school. After I select the school, I go to the next drop down (combo box) and select the players I want. When I go to the second combo box (players) its has a query that runs for me. It takes what ever school I select and only gives me the players from that school.
However, lets say I made a mistake and I selected the wrong school, I go back and select the right school, but when I go to players box, its shows me the players from the previous school.
My questions is, how do you run the query in the form again after you have select a school? When I go to a new record to put in the information, the same thing happens to, I get the players from the school I selected the first time
View 2 Replies
View Related
Sep 6, 2007
Hey guys-
I have 2 identical tables. I want to update the data from Table1 to go into Table2. Each table has well over 70+ fields in them. Instead of handwriting out each [Table].[Field] in either SQL or the Designer- is there a shortcut to tell Access to grab all the fields from Table1 and update all the fields in Table2 (all the fields have the same name)? I just don't have the energy to type it all out- I figure there's got to be a way...
I know when you do an APPEND query in the designer- it will do this for you- but not the UPDATE query...
Thanks!
View 6 Replies
View Related
May 1, 2008
I have a one-to-many query with two tables. I'm using the query as a record source for a subform.
The subform displays the correct data I'm after however I can't update any fields on the it due to the underlying query having a one to many relationship. I understand that and thats fine.
Problem is I need users to be able to update one field on this subform as required. Is there a way to allow this while keeping the underlying query the same?
View 1 Replies
View Related
