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
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
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 msdn.microsoft.com/en-us/library/office/… — Emil Olsen
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

Emil Olsen Emil Olsen · Accepted Answer · 2015-03-18T12:04:51

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.

Changing datasheet cell based on form comboxes

1 Answers