combo populates combo missing operator error 1

[topwaya]

Hi,
I have a form with a combo box called "ChooseName" that has the following Row Source:

SELECT tblPersonal.NameL & ", " & tblPersonal.NameF & IIf(Len(NZ(tblPersonal.NICK,""))>0," """ & tblPersonal.NICK & """ ","") & " " & tblPersonal.SUFFIX AS Name, tblPersonal.PID, tblPersonal.LocW FROM tblPersonal ORDER BY tblPersonal.NameL, tblPersonal.NameF, tblPersonal.SUFFIX;

Basically, what it does (besides put together their last, first, nickname and suffix), is it allows me to type in the name of a person and/or scroll through the combo box to find a person.

In the AfterUpdate Event Procedure I have:

Me![LocW_Combo].RowSource = "SELECT LocW FROM [tblPersonal]" _
 & " WHERE PID=" & Me![ChooseName].Column(2)

I am getting the error:

syntax error (missing operator) in query expression 'PID='.

What I am trying to do is populate a second combo box called LocW_Combo that displays the locations that this person could work at. I want to populate the location that the person chosen in the ChooseName combo box works at based on the information in the tblPersonal table.

Once that is accomplished, what I really want to do is:

1. If the person has no location (LocW) in tblPersonal Then display blank combo box but allow me to scroll and choose a location if I want.

2. If the person has a location (Low) in tblPersonal
Then display that location in combo box, but still allow me to scroll and change the location if I want.

Thanks for any help or suggestions - this is really an awesome forum!!

 

[GingerR]

Column start at 0, so PID is column(1)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

Thank you so much for your quick response! I changed it to column(1) and although now I don't get an error message, I don't get anything in the combo box either.

I checked the Populated Row Source on my LocW_Combo combo box and it reads:

SELECT LocW FROM [tblPersonal] WHERE PID=101870

101870 has a LocW in the tblPersonal table. Is there something I can look for to try to solve the problem?

Thanks so much!!

 

[GingerR]

In the OnEnter of the second combo box, put

Me.[LOCW_Combo].Requery

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

Thanks very much for the suggestion of

Me.[LocW_Combo].Requery

But there is nothing different happening.

Any other suggestions?

Thanks for any help

 

[GingerR]

You put it in the OnEnter of the LOCW_Combo control?

If you build a combo box with that exact rowsource it in from the beginning, does it fill with info?


What's happening is that the RowSource SQL (words) gets changed, but you have to requery the box to make the changed Row Source invoke.

Try it in OnClick, or SET FOCUS events.

or i think you can put it in the OnChange or AfterUpdate event of the first combo box. Just keep trying it out.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

OK, that row source doesn't work regardless.... so I'll have to try to figure out why.
Thanks very much!

 

[GingerR]

is the ID a NUMBER? or TEXT?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

It is an autonumber.

 

[GingerR]

use the build button of the row source of the combo box (the button with the three dots on it). in the query grid put your PID that you know exists. run it. if it works, look at the SQL view and see what's written there.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

Thank you so much for your help.

I did the query grid and the SQL looks like this:

SELECT tblPersonal.LocW FROM tblPersonal WHERE (((tblPersonal.PID)=101870));

When I ran the query in the grid, it returned the location, but the combo box was still blank - so I figured out that I had the bound column(1) set to 0 width.

I never would have figured that out! Now it does show up in the second combo box - Yippee!!!!!

However, I have to click on the arrow to see it.

Is there anyway to do the following?

1. If the person has no location (LocW) in tblPersonal Then display combo box with nothing chosen but allow me to scroll and choose a location if I want.

2. If the person has a location (LocW) in tblPersonal
Then display that location as chosen in the combo box, but still allow me to scroll and change the location if I want.

Thanks so much again!!!!

 

[GingerR]

hi. glad it worked for you. just takes some poking around!

I'm not very clear on your additional requests. instead of "tblPersonal" are you meaning the combo boxes? By "the person" do you mean "the user"? or does "the person" refere to someone chosen in a table that has an associated location? Please restate with the names of your combo boxes and possibly some sample data. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

OK, here we go... (I'll do my best)

[ChooseName] combobox has the following row source:

SELECT tblPersonal.NameL & ", " & tblPersonal.NameF & IIf(Len(NZ(tblPersonal.NICK,""))>0," """ & tblPersonal.NICK & """ ","") & " " & tblPersonal.SUFFIX AS Name, tblPersonal.PID, tblPersonal.LocW FROM tblPersonal ORDER BY tblPersonal.NameL, tblPersonal.NameF, tblPersonal.SUFFIX;

You can either begin to type in a name (and it is auto-completed), or you can scroll through the combobox which displays all the names of all the employees and select a name. (This works perfectly)

Whatever name is selected, I would like the [LocW_Combo] combobox to display the work location [tblPersonal.LocW] of that employee.
*************************************************
So [ChooseName]combobox has selected employee:
Smith, Julie

Now I want the [LocW_Combo] combobox to have Julie Smith's work location selected in the [LocW_Combo] (it is currently located in the combobox, but not already selected)

This is accomplished with this code in the AfterUpdate Event Procedure of the [ChooseName] combobox:

Me![LocW_Combo].RowSource = "SELECT LocW FROM [tblPersonal]" _
 & " WHERE PID=" & Me![ChooseName].Column(1)

****************************************************

So now I would have:
Smith, Julie
who works at:
SFM

****************************************************

Upon seeing this, I realize that Julie Smith has been transfered to a new work location, so I would like to be able to click the arrow in the combo box and choose a new location for Julie Smith using lookup source [tblLoc] which is a table that has all the locations.
******************************************************

And lastly,
What if Julie's work location isn't in the database yet? (sad but true, a common occurence)
Then I would want to have no location selected in the [LocW_Combo] combobox. And I would like to be able to click the arrow in the combo box and choose a location (if I know it, or not if I don't) for Julie Smith using lookup source [tblLoc] which is a table that has all the locations.
********************************************************

Clear as mud? LOL - The more I learn about access and SQL the more I begin to see a little hint of the huge amonth that I don't know .

Thanks much for any help!

 

[GingerR]

Seems like you are trying to use the same item for multiple reasons and it's not really doing what you mean for it to do. The controls are behaving as you have them programmed, but how you have them programmed is not what you want. You are limiting the second combo box to only the things in the table that match a cert PID. Let's back up a little bit. I'm going to assume some things, too, and guess at what you are trying to do.

The form has a Record Source of your table tblPersonal.

Your first combo box is a search combo box. Your intent is that when you choose someone from it, their data gets displayed in text boxes and other controls on the form.

This combo box should not have any Control Source, as it is unbound.

Control Source: none
Bound Column: 1
Number of Columns: 2
Column Widths: 0,2

RowSource:

SELECT tblPersonal.PID, tblPersonal.NameL & ", " & tblPersonal.NameF & IIf(Len(NZ(tblPersonal.NICK,""))>0," """ & tblPersonal.NICK & """ ","") & " " & tblPersonal.SUFFIX AS Name FROM tblPersonal ORDER BY tblPersonal.NameL, tblPersonal.NameF, tblPersonal.SUFFIX;

RESULT:
1 Smith, John Jr.
2 Jones, Robert
3 Barker, Steve


AfterUpdate event of the first combo box:

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PID] = " & Str(Nz(Me![ChooseName], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

This is the same kind of code that Access writes for you if you plunk a combo box on to the form and pick the third choice ("Move to the record I choose in the combo box" or something like that).

Ok, so what we have so far is that you pick a name from the first combo box, and the form moves to that record.


For the second combo box, get rid of all of the code that you just wrote.

The CONTROL SOURCE of the LocW_Combo would be LocW.
The ROW SOURCE would be a table that you have the lists unique Locations:

Row Source: Locations (a table with locations in it)

(example table:
Table: Locations
Field: LocationID
Field: LocationName

SFW San Francisco
HUS Houston
BOS Bostong)

The Bound Column would be 1
Column Count: 2
Column Widths: .5,1

So once you pick Jane Smith and the form moves to her record, the second combo box will show the related Jane Smith location but when you click on the 2nd combo box, the entire list of locations will be present. Since the second combo box is BOUND to the field LocW, if you pick something different, it will just be put into her record.

Why don't you work on getting that going for now, and then we'll work on the "i need to add more locations" thing.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

Yippee!!!!

Gosh - I thought that's what I tried to do before I started asking questions - you make it look so easy to figure out .

Everything works perfectly now!!!

Thank you soooooo much - you don't know how many hours of frustration you've saved me - and learning all the time too - thanks very much for your patience and help!!

 

[topwaya]

how do you add stars to posts?

 

[GingerR]

Glad it worked out for you so quickly! You're a quick study.

Stars = at the bottom of my posts there should be a thing that says "Thank GingerR for this valuable post".

Have fun!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

Thanks Again - Sent you a star

 

[GingerR]

Thanks! You wanna move on to how to add another Location? If so, tell me the structure of your Location table. You have one, right?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[topwaya]

I don't need to add another location.

All I wanted was to be able to choose a new location from the combobox - and I can and it automatically updates their location in the database. Everything works like a charm!

I'm sure I'll be back though - a zillion things still to figure out how to do

Thanks much!!!!