Multiple Combos on form, each with controlsource set to single record? 1

[thefarg]

I have a form with a source set as a table with fields [Number] and [Job]. On the form is 26 Combobox's. I want to have each combobox to have a single record of the underlying table as its controlsource. Currently all the combobox's show the current record, when I increment or move records they all show the same. Anyone have an idea on how to do this or is it just not possible?

 

[dhookom]

A single form has a single record source and shows 1 or multiple records. You can use a continuous form to show multiple records. Each record is displayed as a row in the form.

Depending on your needs you might be able to use subforms or code.

Duane
Hook'D on Access
MS Access MVP

 

[thefarg]

Yes, I figured I would have to code it, but was hoping that Access's underlying structure may be used instead. I can use a function as the controlsource of a combobox but it can only display data, not write it to the table. Anyone have a better idea? Surely this is a common scenario in Access?

 

[dhookom]

It sounds like you might want a display more like a spreadsheet (can't tell for sure). At times I have pushed the data to a temp an un-normalized temp table and then used code and/or queries to move the values.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Surely this is a common scenario in Access?

No. I have never seen anything like that in any application. It sounds like you are trying to make an unbound form act like a continous form without using a bound continous form. I have never seen a bound form in any application where each record is bound to an individual control.

If you want to do this you would use ADO or DAO to create a recordset and populate the controls. Then write back to the recordset once done. Google "Unbound forms". But unless you really need a unbound form, use bound continous forms. Unbound forms give you lots of flexibility but likely way out of your league.

 

[thefarg]

Not at all. I have several unbound forms using DAO.recordet to work with data, however I hoped for a better solution than looping through every control on the form with a recordset.
The limitation is that a function can be used as the argument for the controlsource of a combobox, but can only be used to display data (Dlookup, ado etc).
Googling msdn, I had the thought of using a subquery as the controlsource, but I get an error regarding an operand without an operator. In the following SQL statement, I am entering this into the controlsource properties via the properties dialog for one of the combobox's.
In the Tag property of the combobox is a letter (A-Z) which I am trying to use in the WHERE clause of the SQL

[SELECT tblIdentifiers.[Identifier Name] FROM tblIdentifiers WHERE (tblIdentifiers.[Identifier Letter] = Me.Activecontrol.Tag)]

Can anyone help me as I am unsure on the syntax when using a variable in an SQL statement.

Thanks,
Mike

 

[MajP]

I am usually hesitant to say something cannot be done, but this time I feel comfortable. It is impossible. It will not work. No way, no how. You can not bind a control to a single specific record.

Can you explain what you are trying to accomplish? I can pretty much guarantee that your database is not properly normalized. Desires for Cludgey workarounds like this are usually a sign of a non normal structure.

You could fake it in a couple of ways, but why?
You could move to the appropriate record when moving to the next control. You could have 26 subforms each subform would have a different recordsource that returns one record.

However, to use a variable in a sql statement you need to build a function that returns the value. Then you can use the function in sql.

Any chance you are mixing up the terms control source and rowsource? I could almost understand rowsource.

 

[dhookom]

It would be nice if we had something more descriptive. I think the structure might be normalized but they want an un-normalized format for data entry.

We need some context.

Duane
Hook'D on Access
MS Access MVP

 

[thefarg]

For my rowsource I have a table providing a list of values. That is not a problem. What I want is the SQL equivalent of a

dlookup ("Column", "Table", "Row =" & Variable)

where Variable is actually data stored in the .Tag property of the combobox.
So Combox has "A" property in .Tag, it matches record with "A" in the field im looking up.
Microsoft has an article on MSDN showing how to use a variable in SQL but i am confused with the multiple """" '''' etc.

Would I be better off asking in a more SQL oriented part of the forum or does someone here have a solution. Even some information on how to use a variable in SQL would be great.

 

[MajP]

No do not double post. The same people review the forums. The issue is not the forum, the issue is that no one can understand what and why you are trying to do.

If you are not certain of our credentials you can click on our names and read our stats on Tek-Tips. Both Duane and I are recognized Microsoft MVPS with around 20,000 replies on Tek-Tips. If this does not make sense to us, you are unlikely to find someone who will tell you it does. The only way you are going to get an answer is stop telling us what you are trying, and clearly explain what you desire. What you are trying will not work and will never work.


To use a variable in sql you need to build a function, or if it is a form control you can reference it directly

public function getSomeVariable() as variant
  dim someVariable
  'code here
  getSomeVariable = someVariable
end function

in sql

select .... where somefield = getSomeVariable

or if it is in a open form

select ..... from sometable where somefield = forms!FormName!ControlName.PropertyName

IT STILL DOES NOT MATTER. YOU CANNOT MAKE THAT A CONTROLSOURCE. YOU CANNOT BIND A CONTROL TO A SPECIFIC RECORD. Controls are bound to fields or they are calculated (one way).

 

[thefarg]

Oh. I had the impression that the controlsource could be a query. And that a query could return a single record. So putting it together, I figured that the controlsource could be based off of a query returning a single record. A control ( like a combobox) acts as a window to the data in only 1 record at a time, having to move to each record to act on it. What is the minimum amount of records that can be used as a in a query as a controlsource? 2? Why is it not feasible to use a query returning one record?

YOU CANNOT BIND A CONTROL TO A SPECIFIC RECORD. Controls are bound to fields

I am trying to bind to a field and use the where clause to filter it to a group of records.... That group just happens to only have one record in it.
What is the format (including brackets etc) for entering the example given into the controlsource property box in design mode?

 

[thefarg]

I have tried

=[SELECT tblIdentifiers.* FROM tblIdentifiers WHERE tblIdentifiers.[Identifier Letter] = Me.Activecontrol.Tag]]

but it comes up wth an error - The syntax of the subquery is incorrect, - check the subquerys syntax and enclose it in parentheses.
If I do so it tells me that expressions need to be started with =, however none of my other subquerys has needed it.

Any idea what I am doing wrong?

 

[MajP]

Everything!
I do not know anymore ways to say this. IT CANNOT BE DONE.

Can you stop wasting everyones time and explain in detail what you are trying to do, and not what you are attempting. If you clearly explain the what and why we can provide some possible work around.

 

[dhookom]

I believe you can use a control source like:

=DLookup("[YourFieldname]","tblIdentifiers","[Identifier Letter] = '" & [Activecontrol].[Tag] & "'")

This assumes Identifier Letter is text. I'm not sure what happens as you move through various controls on your form.



Duane
Hook'D on Access
MS Access MVP

 

[MajP]

But what whould be the utility? You would have an uneditable combobox that only displays the value in a given record? If that was the end state you could do it lots of easier ways.

 

[MajP]

theFarg,
Maybe we are confused. Maybe you just want to know how to build a continous form. Is that what you are asking? You can have a single control. You will not need 26. It is such a fundamental concept that it may have confused us. If that is the case, simply bind your control to the appropriate field and switch the forms default view to continous.

 

[thefarg]

My understanding is that a continuous form is like looking at a datasheet. Is that correct?

 

[thefarg]

using

=DLookup("[YourFieldname]","tblIdentifiers","[Identifier Letter] = '" & [Activecontrol].[Tag] & "'"

Doesnt give me any result. Using the same dlookup in the OnLoad event works fine. I have however discovered that having anything but a blank or bound controlsource makes the .Text property of the combo read only.

 

[dhookom]

thefarg,
You are going way unconventional with your attempts. You will not be able to enter anything into a text box with a control source beginning with "=". If you actually used

=DLookup("[YourFieldname]","tblIdentifiers","[Identifier Letter] = '" & [Activecontrol].[Tag] & "'"

then you made at least 2 serious mistakes. You need to include your actual field name in place of [YourFieldname] and there is a missing ")".

A continuous form can look very different from a datasheet.

Access provides a lot of great functionality out of the box. I'm not sure why you don't take advantage of the easy to use features.

Duane
Hook'D on Access
MS Access MVP

 

[thefarg]

I have changed my approach and now I am using Seek with the Recordset of "tblIdentifiers". Because the combobox's are unbound, selecting something from the list of any of the combo's doesn't trigger the forms onChange, dirty etc events. It works if I use the individual combobos's onChange event but that will mean setting it for 26 combox's and it looks untidy. Is there a better way to poll for multiple objects events (they all open the same Function anyway) ?