ComboBox - ' Data cannot be retrieved' error

[Evi]

I'm using Access 97
When I try to create a combo box with a particular query as a data source I
get the message
'Data cannot be retrieved from the source you have selected. You must select
a different table or query to continue the wizard'
I can create a ComboBox without using the Wizard but when I try to select a
different row in the combobox it won't change the record which is displayed
unless I move the slider bar in the combo box down.
The Combo is Unbound
A combo box in the same form but based on a different query works fine.
A bound combo in another form based on the problem query works.
Even more oddly, when I copied the working combo from the other form, pasted
it into my problem form and unbound it, it immediately worked.
I am on a stand-alone PC - no network.
The MS Knowledge base does refer to this problem but relates it to later
versions of Access and recommends updating to the latest version of MDAC
(can't understand the explanation of what this is). I have a horrible
suspicion that a) this will not solve the problem b) it will cause other
problems with MS office.
I have never seen this problem before and all my other dbs work fine.
I have no missing references.
I see a couple of letters re this in Google but I have not found that anyone
actually solved the problem.
Could it be that the query Sql is too long?

SELECT TblAddress.NmID, IIf([TblAddress].[NmID]=1,"None",IfNul([FName]," ")
& IfNul([LtName]," ") & IfNul([Comp],"")) & " " &
IIf([AddrID]=1,"None",IfNul([HsNo]," ") & IfNul([Street]," ") &
IIf(IsNull([Abr]),[Town],[Abr])) AS NameAddr, TblAddress.AddrID,
TblName.FName, TblName.LtName, TblName.Comp, TblAddress.HsNo,
TblStreet.Street
FROM TblTown INNER JOIN (TblStreet INNER JOIN (TblName INNER JOIN TblAddress
ON TblName.NmID = TblAddress.NmID) ON TblStreet.StID = TblAddress.StID) ON
TblTown.TownID = TblStreet.TownID
ORDER BY TblName.FName, TblName.LtName, TblName.Comp, TblAddress.HsNo,
TblStreet.Street;


IfNul is a function I wrote
The code is as follows.

Function IfNul(Fld As Variant, Mrk As String) As String
'used to save letters on long iif phrases
'so as to avoid the character limit on a formula
'in a query.
'Especially useful for Name Address fields
'If the field in Null, print a "" else print the field and a string
'usually a space or a comma space
If IsNull(Fld) Or Fld = "" Then
IfNul = ""
Else
IfNul = Fld & Mrk
End If
End Function

Has anyone ever solved this problem?
Evi

 

[Evi]

I'm using Access 97
When I try to create a combo box with a particular query as a data source I
get the message
'Data cannot be retrieved from the source you have selected. You must select
a different table or query to continue the wizard'
I can create a ComboBox without using the Wizard but when I try to select a
different row in the combobox it won't change the record which is displayed
unless I move the slider bar in the combo box down.
The Combo is Unbound
A combo box in the same form but based on a different query works fine.
A bound combo in another form based on the problem query works.
Even more oddly, when I copied the working combo from the other form, pasted
it into my problem form and unbound it, it immediately worked.
I am on a stand-alone PC - no network.
The MS Knowledge base does refer to this problem but relates it to later
versions of Access and recommends updating to the latest version of MDAC
(can't understand the explanation of what this is). I have a horrible
suspicion that a) this will not solve the problem b) it will cause other
problems with MS office.
I have never seen this problem before and all my other dbs work fine.
I have no missing references.
I see a couple of letters re this in Google but I have not found that anyone
actually solved the problem.
Could it be that the query Sql is too long?

SELECT TblAddress.NmID, IIf([TblAddress].[NmID]=1,"None",IfNul([FName]," ")
& IfNul([LtName]," ") & IfNul([Comp],"")) & " " &
IIf([AddrID]=1,"None",IfNul([HsNo]," ") & IfNul([Street]," ") &
IIf(IsNull([Abr]),[Town],[Abr])) AS NameAddr, TblAddress.AddrID,
TblName.FName, TblName.LtName, TblName.Comp, TblAddress.HsNo,
TblStreet.Street
FROM TblTown INNER JOIN (TblStreet INNER JOIN (TblName INNER JOIN TblAddress
ON TblName.NmID = TblAddress.NmID) ON TblStreet.StID = TblAddress.StID) ON
TblTown.TownID = TblStreet.TownID
ORDER BY TblName.FName, TblName.LtName, TblName.Comp, TblAddress.HsNo,
TblStreet.Street;


IfNul is a function I wrote
The code is as follows.

Function IfNul(Fld As Variant, Mrk As String) As String
'used to save letters on long iif phrases
'so as to avoid the character limit on a formula
'in a query.
'Especially useful for Name Address fields
'If the field in Null, print a "" else print the field and a string
'usually a space or a comma space
If IsNull(Fld) Or Fld = "" Then
IfNul = ""
Else
IfNul = Fld & Mrk
End If
End Function

Has anyone ever solved this problem?
Evi

Oh well, I think I know what MAY be the problem. The Bound Column in the
Combo box was not a unique number - I wanted to retrieve a set of records
with the combo.
In the query, I moved the unique field to the first column, put the number I
wanted into the third column then used

Me.cboContact.Column (2)

to get the field I really wanted.

Once I did this, everything was fine.
Evi