Referencing controls in SQL query .... er ... how??

  • Thread starter Thread starter TimS
  • Start date Start date
T

TimS

Hi All,

Feel really stupid here - but cannot find it in anywhere.

I have a nice application running in access - have ported
the tables and views to SQL Server, with ~ 20 users at
the moment. Runs fine with linked tables/views connected
to the old forms and reports.

Now moving all to an access project and .... half of the
forms break-down - as I have used controls on the forms
that reference another forms text or list control as part
of an SQL view. SQL does not like this - and falls over.

So the question - How to reference a text box object in
an SQL view??

Grateful for your help!!!!!

Tim
 
Witaj TimS,
W Twoim liœcie datowanym 28 kwietnia 2004 (22:40:57) mo¿na przeczytaæ:

T> Hi All,
T> Feel really stupid here - but cannot find it in anywhere.
T> I have a nice application running in access - have ported
T> the tables and views to SQL Server, with ~ 20 users at
T> the moment. Runs fine with linked tables/views connected
T> to the old forms and reports.
T> Now moving all to an access project and .... half of the
T> forms break-down - as I have used controls on the forms
T> that reference another forms text or list control as part
T> of an SQL view. SQL does not like this - and falls over.
T> So the question - How to reference a text box object in
T> an SQL view??

You cannot reference a textbox in "SQL View" direct.
You can:
1. Change a view (Sql code!) after every change (do not recommend -
lose optimalization)
2. Use a stored procedure and change value of parameters via VB

After every change
MyCombobox.RowSource = "exec MyStoredProcedure @Parameter1
datatype1=" & me.MyField

for example:
MyCombobox.RowSource = "exec MyStoredProcedure @myfavoriteparameter1
varchar(50)='" & me.myfavoriteoption & "'"

MyCombobox.RowSource = "exec MyStoredProcedure @myfavoriteparameter1
varchar(50)='" & me.myfavoriteoption & "', @myfavoriteparameter2
int=" & me.myfavorite_int_textbox

What about "main" forms?

If its source of form/report you should put into RecordSource field
of this particular form/report name of procedure:
dbo.MyFirstProcedure
and into InputParameters field:
@myfirstparameter varchar(255)=[Forms]![myform]![mytextbox]

Regards
Jacek Segit
 
Thanks Jacek,

I fiddled with this for a moment - and now it works - many
thanks!!!!

The below is for a combobox that I just fixed :

Though - one question - when I add varchar(50)in the
statement it does not return records, when I leave the
varchar(50) out - it returns (the correct) records - so
that I am not storing up problems for the future - why is
that?

Thanks again!!

Tim
 
Witaj TimS,
W Twoim liœcie datowanym 29 kwietnia 2004 (17:38:23) mo¿na przeczytaæ:

T> Thanks Jacek,
T> I fiddled with this for a moment - and now it works - many
T> thanks!!!!
T> The below is for a combobox that I just fixed :
T> Though - one question - when I add varchar(50)in the
T> statement it does not return records, when I leave the
T> varchar(50) out - it returns (the correct) records - so
T> that I am not storing up problems for the future - why is
T> that?

Oh, my error.
Obviously in combobox.rowsource you shouldn't add datatype, but its necessary
in InputParameters field.

Regards
Jacek Segit
 
Thanks very much Jacek - this works.

But ..... (yes indeed a question is coming on ....!)

When I set the recordsource as a stored procedure rather
than a view - form becomes uneditable .... better to use a
form filter??

Thanks!!!

Tim
 
Witaj TimS,
W Twoim liœcie datowanym 2 maja 2004 (22:53:07) mo¿na przeczytaæ:

T> Thanks very much Jacek - this works.

T> But ..... (yes indeed a question is coming on ....!)

T> When I set the recordsource as a stored procedure rather
T> than a view - form becomes uneditable .... better to use a
T> form filter??

Which version of MSAccess do you use? 2000? XP? 2003?
How do you set recordsource? Via VB or manually?

Regards
Jacek Segit
 
Hi Jacek,

Access 2002/XP + Windows 2000

I open and set the record set of the form as below :

Where the Me.xxx = comboboxes

**********************************
DoCmd.OpenForm "Forecast"

Forms!Forecast.RecordSource = "SP_Act_Cus_Prod_Cur"

Forms!Forecast.InputParameters = "@Cus varchar(50)='" &
Me.L_customer & "', @Product varchar(50)='" & Me.L_Product
& "', @Ph12NC varchar(50)=" & "''"

Forms!Forecast.SetFocus
***********************************

The stored procedure just uses the view with these 3
parameters. When the recordsource is set to the same
filtered view - it is editable. I think I am missing
something on the structure of SQL/Access.

Thanks again

Tim
 
Witaj TimS,
W Twoim liœcie datowanym 3 maja 2004 (21:12:22) mo¿na przeczytaæ:

T> Hi Jacek
T> Access 2002/XP + Windows 2000
T> I open and set the record set of the form as below :
T> Where the Me.xxx = comboboxes
T> **********************************
T> DoCmd.OpenForm "Forecast"
T> Forms!Forecast.RecordSource = "SP_Act_Cus_Prod_Cur"
T> Forms!Forecast.InputParameters = "@Cus varchar(50)='" &
T> Me.L_customer & "', @Product varchar(50)='" & Me.L_Product
T> & "', @Ph12NC varchar(50)=" & "''"
T> Forms!Forecast.SetFocus
T> ***********************************

What about permissions? You use 'sa' login or other?
Try to check InputParameters
debug.print "@Cus varchar(50)='" & _
Me.L_customer & "', @Product varchar(50)='" & Me.L_Product & _
"', @Ph12NC varchar(50)=" & "''"


....Finally...
Why have you not write Recordsource and InputParameters constantly?
(I don't know, maybe its matter of architecture)
IMHO change Me.xxxx to Forms!YourForm!YourControl
and try to write RecordSource and InputParameters constantly

(I've checked in my Acc and found that is bug - after changing
via VB InpuParameters no matter is that I set particulary value of
parameter - I always see DialogBox for value of this parameter)

T> The stored procedure just uses the view with these 3
T> parameters. When the recordsource is set to the same
T> filtered view - it is editable.

But If you set to stored procedure, can you browse this uneditable
records or you've got only blank form?

Can you write your StoredProcedure (if its not Top Secret :P)?

Regards
Jacek Segit
 
Back
Top