Pass a control.value to SQL statement??

[Chris K]

I have made a query to use as a rowsource in a combo on a form, the query
suggests likely email addresses which is quite ambiguous and not really
relevant

SELECT IIf(InStr([rname]," "),Left([rname],InStr([rname]," ")-1) & ".","") &
Mid([rname],InStr([rname]," ")+1) & [domain] AS Expr1 FROM Referer, [email
domain] WHERE (((Referer.RID)=me.rid));

the SQL statement works fine but i need to pass the forms RID value to the
'where' part of the statement

WHERE (((Referer.RID)=me.rid));

but it's not happy about that

what's the correct syntax for that??

 

[John W. Vinson]

I have made a query to use as a rowsource in a combo on a form, the query
suggests likely email addresses which is quite ambiguous and not really
relevant

SELECT IIf(InStr([rname]," "),Left([rname],InStr([rname]," ")-1) & ".","") &
Mid([rname],InStr([rname]," ")+1) & [domain] AS Expr1 FROM Referer, [email
domain] WHERE (((Referer.RID)=me.rid));

the SQL statement works fine but i need to pass the forms RID value to the
'where' part of the statement

WHERE (((Referer.RID)=me.rid));

but it's not happy about that

what's the correct syntax for that??

SQL doesn't know the Me. keyword; use the full reference

(((Referer.RID) = [Forms]![NameOfForm]![rid]))
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Chris K]

I have made a query to use as a rowsource in a combo on a form, the query
suggests likely email addresses which is quite ambiguous and not really
relevant

SELECT IIf(InStr([rname]," "),Left([rname],InStr([rname]," ")-1) & ".","")
&
Mid([rname],InStr([rname]," ")+1) & [domain] AS Expr1 FROM Referer, [email
domain] WHERE (((Referer.RID)=me.rid));

the SQL statement works fine but i need to pass the forms RID value to the
'where' part of the statement

WHERE (((Referer.RID)=me.rid));

but it's not happy about that

what's the correct syntax for that??

SQL doesn't know the Me. keyword; use the full reference

(((Referer.RID) = [Forms]![NameOfForm]![rid]))
--

Works fine except on a continuous form - always refers to the same record/ID
irrespective of which one I'm editing

So although there's 8 combos (8 records same field) - all combo lists are
the identical (seem to be based upon the first record/combo i try)

Thanks anyway - I'll see if I can figure it

Maybe requery the combo(s) in form current event

 

[Chris K]

I have made a query to use as a rowsource in a combo on a form, the query
suggests likely email addresses which is quite ambiguous and not really
relevant

SELECT IIf(InStr([rname]," "),Left([rname],InStr([rname]," ")-1) &
".","") &
Mid([rname],InStr([rname]," ")+1) & [domain] AS Expr1 FROM Referer,
[email
domain] WHERE (((Referer.RID)=me.rid));

the SQL statement works fine but i need to pass the forms RID value to
the
'where' part of the statement

WHERE (((Referer.RID)=me.rid));

but it's not happy about that

what's the correct syntax for that??

SQL doesn't know the Me. keyword; use the full reference

(((Referer.RID) = [Forms]![NameOfForm]![rid]))
--

Works fine except on a continuous form - always refers to the same
record/ID irrespective of which one I'm editing

So although there's 8 combos (8 records same field) - all combo lists are
the identical (seem to be based upon the first record/combo i try)

Thanks anyway - I'll see if I can figure it

Maybe requery the combo(s) in form current event

It did:

Private Sub Form_Current()
Remailcombo.Requery
End Sub

Form Current event knows which specific record is selected on a continuous
form which has answered some other issues too

Thanks for input it works fine now

 

[David W. Fenton]

Private Sub Form_Current()
Remailcombo.Requery
End Sub

Why not:

Me!Remailcombo.Requery

Or:

Me.Remailcombo.Requery

It's always bad practice to not specify the parent of a control,
because it makes the code difficult to read (is Remailcombo a
control or a variable?). If it's actually a variable, I think it's
badly named, as it looks like a control name.