Changing datasheet cell based on form comboxes

Question

I'm trying to figure out how I can create a form with combo boxes that uses the main table while the combo boxes use a query. What I'm trying to do is get the combo boxes to update after they are changed. They're 65 combo boxes each with it's own default (1 - 65). The main table has a column named "Recall_Position" and the query pulls from this and the "Lastname" and "Firstname". When the form is opened each combobox is auto filled with the last name of the from the row where the "Recall_Position" is equaled to the comboboxes default.

Example1: the row that contains the number one in "Recall_position" has the name Smith in the same row under "Lastname", when the form is opened the combobox that has "1" set as it's default will open with Smith already selected in the combobox. This is already set and working with no issues.

What I'm trying to get is when a different "Lastname" is selected in the combobox it will clear the number from the "Lastname" and set the new "Lastname" with that comboboxes default number.

Example2: Using Example1 when the combobox is changed from Smith to Woods, it will find Smith in "Lastname" and go to the same row under "Recall_position" and clear Smith's number then find Woods and put the "Recall_position" as "1".

If I have to go through and add a VBA fro each and every combobox on change, it wouldn't bother me. The reason behind this is I am creating a report that will pull information based on the "Recall_position" to create roster of names and phone numbers.

combo boxes

Not sure i understand - Can you put upload a screenshot? What combobox do you want to change? All 50? — Emil Olsen, Mar 02 '15 at 13:37
The procedure is hard to understand only from words. Try and update your question with some pictures and step by step description. I think other people find it hard too, since no one replied so far. — Emil Olsen, Mar 05 '15 at 08:15
As i understand example 1, you want the default value to be a variable. A variable that is set from the latest chosen name. If you have ="1" in default value, you cant change the default value. You need to go through VBA. Take a look at this for a start https://msdn.microsoft.com/en-us/library/office/aa195837%28v=office.11%29.aspx — Emil Olsen, Mar 17 '15 at 15:04
Example 2 sounds possible. You need to enter the recordset from VBA (Do you know how to do so?). You can then search for the value in Combobox_1 (the combo box you talk about), and then swap the numbers or whatever you want to do. Do you have an idea about how the recordset works? — Emil Olsen, Mar 17 '15 at 15:06
Example one is already set and in effect with no issues. 65 combo boxes with their defaults ranging from 1 to 65, each combo box has its own default number. When I go into the datasheet and change all the numbers manuly it opens with the correct names. No issues. — user2002716, Mar 18 '15 at 11:05
I know that I will have to use a VBA for each box individually to, on change, find the values and then change them. I've been looking (searching the web and the help) but I can't find the VBA I'm looking for. Plus it's not like excel where you can record and then clean the code up. So I'm stuck between a rock and a hard place. — user2002716, Mar 18 '15 at 11:09
Okay see my answear in a few mins about changing the values. — Emil Olsen, Mar 18 '15 at 11:35

score 0 · Answer 1 · answered Mar 18 '15 at 12:04

Try using this for example 2:

Dim dbs As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set dbs = CurrentProject.Connection
  With rst
       Set .ActiveConnection = CurrentProject.Connection
       .Source = "TheTableYouHave"
       .CursorLocation = adUseClient
       .CursorType = adOpenKeyset
       .LockType = adLockOptimistic
       .Open
   End With
rst.MoveFirst

rst.Find "[Recall Position] "27"" ' Will go to line number that hold 27
OldLastName = rst!LastName ' Save the old last name
rst!LastName = NewLastName ' Replace lastname in line 27 with new last name

set dbs = Nothing
set rst = Nothing

etc. etc.

You can also just replace the rst![Recall Position] with another number. It was just to give you an idea of how to do this. When you use rst. something you tell the recordset what to move to, search for etc. and when it is rst! something, you go into the recordset to read a cell or replace a cell. It is not like excel. You can not do a cells(2,5). In this case it will be:

rst.move 5
rst![Column 2 Name] = NewValue

Which corresponds to

cells(2,5) = NewValue

in excel (sorry if i mixed up rows and columns). I hope you understand. Try using the Debug.Print rst!WhateverYouLike in VBA and get an idea of the codes.

Best regards, Emil.

That's kind of close. I'm not trying to change any information on a row, other than the number in Recall_position. What I'm trying to find or get help with is say the combobox with "1" set as its default value is named combo1. So a code that starts as Private Sub Combo1_Change() find "1" in table("Userdata") column "Recall_Position" and clear it. Then find the last name that the combo box was changed to and in the same row under "Recall_position" put the number 1 — user2002716, Mar 18 '15 at 15:44
I would still say you need the code i provided. If you want to move around numbers in a table, you need the code above. You can not do it otherwise except for making a query and relate to that... If you want to swap f.x. number 1 and 5, so that 5 goes to line 1 and 1 to line 5, you need to use the above written code. — Emil Olsen, Mar 18 '15 at 20:04
To change the number in the row do a rst![Recal_Position] = 5 (if you want it to be 5). — Emil Olsen, Mar 18 '15 at 20:07

Changing datasheet cell based on form comboxes

1 Answers1