Setting an unbound object

[Anthony]

I thought I could set an unbound object in a form using VBA just by doing a
Me.<unbound field name>.RowSource = "<Select statement>". Apparently, that
doesn't ACTUALLY set the field, it only queries the table to give you the
list of data in the combo field. Once you do the query, how do you
actually set the unbound field with the data that was returned from the
Rowsource query? I would like to set the field programmatically without user
input.

Thanks

 

[Beetle]

First, just as a point of clarification, tables and queries have fields, forms
and reports have controls. You can have an unbound "control", but there is
no such thing as an unbound field.

Second, if this is a combo box, then isn't the user going to need to make
a selection? And would that not qualify as user input?

I only ask because you may be making things more complicated than they
need to be by having an unbound combo box and trying to update the
table programatically. Are you trying to perform some additional verification
of the data before it gets saved to the table?

If you can provide some more information about what you are doing and why,
someone may be able to offer an appropriate solution

 

[Anthony]

Beetle, your response was very fair. Maybe I need some clarification.

I have a form that lists projects to be completed. By each project is a
project director's name who is responsible for that project. I had the idea
to highlight (using conditional formating) the projects that pertain to each
user. So when I am looking at the project form, my projects are
highlighted. The database knows who are are because you have to sign onto
the database. Your sign on user ID is a number - mine is 20. This number is
one field in a 3 field Security table that also has a First Name field and a
Last name field. So, what I thought I needed to do was look up who you are
(it is set in a Global variable when you sign into the database) and compare
it to the "Project Director" field on the project form - if it matches -
highlight the field. Unfortunately, in the "project Director" field is the
person's actual name, not his User ID #. The Global Variable has your
userID - a number. So I first have to look up the person's name using the
UserID #, then put the person's actual name in a control. That is where
the unbound control comes in, which is a combo box control. I can look up
the person's name just fine by querying the Security Table using a "WHERE the
UserID is = <Global Variable>. That works great! When I click on the combo
box, it shows me my name. However, apparently, it doesn't set the name
into the unbound control so that I can compare it to the "Project Director"
name and highlight the field.
Any help would be appreciated.

 

[Beetle]

If the control that holds the Project Director's name is a combo box, then
you should be able to add the UserID field from the security table to the
row source of that combo box, and then compare it to your global variable
using the column property of the combo box. The column property uses a
zero based index, so the first column is Column(0), second is Column(1), etc.

So, for example, if you add the UserID from the security table as the third
column on your Project Director combo box, then you could compare it to
the global variable like;

If Me.cboYourCombo.Column(2) = YourGlobalVariable Then
do something
End If

*However* - having said that, if this is in a continuous form it still may not
work the way you want. The reason is that trying to use VBA to set conditional
formatting on a continuous form usually doesn't work because it will set the
condition for all of the rows on the form, whether the other rows meet the
condition or not. You can use the built in conditional formatting (from the
Format menu) to properly handle conditional formatting on a continuous
form, but I don't think you can refer to a global variable when using the
built in conditional formatting. I've never actually tried this, so I may be
wrong.
You'll have to try it and see if it works I guess. Again, this is only if
you are
using a continuous form. If not, then VBA conditional formatting should
work for you.

 

[Anthony]

After submitting my question, I did some experimentation and I realized that
if I put the UserID field on the form as a non-visible control, I can
compare my Global Variable to that and it works. I was overthinking the
solution rather than going with something simple. Your solution is even
more elegant in that it doesn't require an additional control on the form;
however, it does require slightly more complicated VB programming - I wonder
which solution would run faster. BTW: you say I can add the UserID as
another column. However, the Project Director control is NOT a combo box -
just a text box that is populated programmatically with a SELECT statement on
the "Recordsource" of the form.

. . . I don't think you can refer to a global variable when using the built in conditional formatting.

You may be right. I couldn't get the conditional formatting to work. I put
the global variable on the form using an non-visible unbound control and
*viola* now it works.

Thanks for your help