Problem turning off action messages in runtime - append query not working

[Lee]

Hello All,

First of all I would like to say thank you for all of the help I have
received here. I have been teaching myself Access for about 4 years
now and I've always been able to find a solution here - until now.
This one is driving me crazy.

I am making my first attempt at creating a runtime application. I am
using Access 2003 Developer Extensions. Initially I developed the
database without planning on creating a runtime app but things have
progressed and I wanted to distribute it to those who don't have MS
Access.

My problem is trying to run an append query without getting the confirm
messages.

I have a main form with a subform. On the subform is a button that
appends records from the subform into a header and detail table (if the
record does not exist) and opens another form to display the records.
Too append the records I am using two queries. This has always worked
for me perfectly well until I tried the runtime version on a PC without
Access.

I researched and found different things to try and all of them either
do not append the records or give a parameter error.

The full code behind the button is:

Private Sub CmdOpenDetails_Click()
On Error GoTo Err_CmdOpenDetails_Click

'Verify that Race Date, Race Track and Race Name are entered.

If IsNull(Me.Track) Then
MsgBox "Please enter a track.", vbCritical, "No Track entered."
Me.Track.SetFocus
GoTo Done
Else

If IsNull(Me.RaceDate) Then
MsgBox "Please enter a Race Date.", vbCritical, "No Race Date
entered."
Me.RaceDate.SetFocus
GoTo Done
Else

If IsNull(Me.RaceName) Then
MsgBox "Please enter a Race Name.", vbCritical, "No Race Name
entered."
Me.RaceName.SetFocus
GoTo Done
End If
End If
End If
DoCmd.RunCommand acCmdSaveRecord

Dim stDocName As String
Dim stLinkCriteria As String

On Error GoTo Err_CmdOpenDetails_Click

DoCmd.OpenQuery ("qryappendtblraceheader")
DoCmd.OpenQuery ("qryappendracedetail")

stDocName = "subfrmracedetails"

stLinkCriteria = "([TrackName]=" & "'" & Me![Track] & "') AND
([RaceName]= """ & Me![RaceName] & """) AND [RaceDate]=" & "#" &
Me![RaceDate] & "#"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdOpenDetails_Click:
Exit Sub

Err_CmdOpenDetails_Click:
MsgBox Err.Description
Resume Exit_CmdOpenDetails_Click

Done:

End Sub

I have attempted to turn off the action confirms with:

the execute method - this caused a "need 3 parameters error"
Setoption "Confirm Action Queries", False - this goes to the next form
but without appending the records
Docmd.setwarnings False - same result as setoption
Application.SetOption "Confirm Action Queries", 0 on load

My latest try was creating a macro turning off the messages and then
turning them back on. That also moved to the next form but did not
append the records.

I really don't understand what I'm doing wrong and I've run out of
things to try.

I would really appreciate any help at all with this and if I haven't
provided enough information please let me know.

Thank you,

Lee

[Tim Marshall]

Lee wrote:

the execute method - this caused a "need 3 parameters error"

Did you try the dbFailOnError option in the execute method?

Access.CurrentDb.Execute "QryName", dbfailonError

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Lee]

Thanks for the reply.

I couldn't remember so I just tried it again and I'm still getting the
error "Too few parameters. Expected 3" And that is using the full
Access program.

Thanks,

Lee
Tim Marshall wrote:

Lee wrote:

the execute method - this caused a "need 3 parameters error"

Did you try the dbFailOnError option in the execute method?

Access.CurrentDb.Execute "QryName", dbfailonError

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Lee wrote:

Thanks for the reply.

I couldn't remember so I just tried it again and I'm still getting the
error "Too few parameters. Expected 3" And that is using the full
Access program.

I usually construct my SQL dynamically (in VBA) rather than used stored
querydefs. I do recall there were sometimes issues using stored queries
and the execute method. Can you post the SQL of the query(ies) that's
causing this? Unless you figure out or someone comes up with a better
solution, of course. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Lee]

I tried to do that when I was just working on getting the data to
update another table based on the form and subform and it was beyond
me.

The append query is built upon another query which I use to get
information from the main table (tblhorse) and the race table
(tblraces).

qryhorseandrace

SELECT tblraces.RaceID, tblhorse.Name, tblhorse.Trainer,
tblhorse.Owner, tblraces.Result, tblraces.Time, tblraces.Notes,
tblraces.Jockey, tblraces.Beyer, tblraces.Weight, tblraces.PP,
tblraces.TrackName, tblraces.RaceName, tblraces.RaceDate,
tblraces.Distance, tblraces.RaceType, tblraces.Surface
FROM tblhorse INNER JOIN tblraces ON tblhorse.ID = tblraces.HorseID;

qryappendraceheader

INSERT INTO tblraceheader ( TrackName, RaceName, RaceDate, Distance,
RaceType, Surface )
SELECT DISTINCT qryhorseandrace.TrackName, qryhorseandrace.RaceName,
qryhorseandrace.RaceDate, qryhorseandrace.Distance,
qryhorseandrace.RaceType, qryhorseandrace.Surface
FROM qryhorseandrace LEFT JOIN tblraceheader ON
(qryhorseandrace.RaceDate=tblraceheader.RaceDate) AND
(qryhorseandrace.RaceName=tblraceheader.RaceName) AND
(qryhorseandrace.TrackName=tblraceheader.TrackName )
WHERE
(((qryhorseandrace.TrackName)=Forms!frmhorse!subfr mraces!TrackName) And
((qryhorseandrace.RaceName)=Forms!frmhorse!subfrmr aces!RaceName) And
((qryhorseandrace.RaceDate)=Forms!frmhorse!subfrmr aces!RaceDate) And
((tblraceheader.TrackName) Is Null) And ((tblraceheader.RaceName) Is
Null) And ((tblraceheader.RaceDate) Is Null));

The query to append the details also is based on another query (based
on another query) to find out if the race details have already been
added.

qryfindmissingrace

SELECT qryhorseracedetail.RaceID, qryhorseracedetail.Name,
qryhorseracedetail.Trainer, qryhorseracedetail.Owner,
qryhorseracedetail.Result, qryhorseracedetail.Time,
qryhorseracedetail.Notes, qryhorseracedetail.Jockey,
qryhorseracedetail.Beyer, qryhorseracedetail.Weight,
qryhorseracedetail.PP, qryhorseracedetail.TrackName,
qryhorseracedetail.RaceName, qryhorseracedetail.RaceDate,
qryhorseracedetail.Distance, qryhorseracedetail.RaceType
FROM qryhorseracedetail LEFT JOIN tblracedetails ON
qryhorseracedetail.RaceID = tblracedetails.RaceID
WHERE (((tblracedetails.RaceID) Is Null));

qryhorseracedetail

SELECT tblraceheader.RaceID, qryhorseandrace.Name,
qryhorseandrace.Trainer, qryhorseandrace.Owner, qryhorseandrace.Result,
qryhorseandrace.Time, qryhorseandrace.Notes, qryhorseandrace.Jockey,
qryhorseandrace.Beyer, qryhorseandrace.Weight, qryhorseandrace.PP,
qryhorseandrace.TrackName, qryhorseandrace.RaceName,
qryhorseandrace.RaceDate, qryhorseandrace.Distance,
qryhorseandrace.RaceType
FROM tblraceheader INNER JOIN qryhorseandrace ON
(tblraceheader.RaceDate = qryhorseandrace.RaceDate) AND
(tblraceheader.RaceName = qryhorseandrace.RaceName) AND
(tblraceheader.TrackName = qryhorseandrace.TrackName);

qryappendracedetails

INSERT INTO tblraceheader ( TrackName, RaceName, RaceDate, Distance,
RaceType, Surface )
SELECT DISTINCT qryhorseandrace.TrackName, qryhorseandrace.RaceName,
qryhorseandrace.RaceDate, qryhorseandrace.Distance,
qryhorseandrace.RaceType, qryhorseandrace.Surface
FROM qryhorseandrace LEFT JOIN tblraceheader ON
(qryhorseandrace.RaceDate=tblraceheader.RaceDate) AND
(qryhorseandrace.RaceName=tblraceheader.RaceName) AND
(qryhorseandrace.TrackName=tblraceheader.TrackName )
WHERE
(((qryhorseandrace.TrackName)=Forms!frmhorse!subfr mraces!TrackName) And
((qryhorseandrace.RaceName)=Forms!frmhorse!subfrmr aces!RaceName) And
((qryhorseandrace.RaceDate)=Forms!frmhorse!subfrmr aces!RaceDate) And
((tblraceheader.TrackName) Is Null) And ((tblraceheader.RaceName) Is
Null) And ((tblraceheader.RaceDate) Is Null));
Wow, this looks like a convoluted mess now that I'm seeing it laid out.
It did take me a long time and several efforts to get it working at
all and it's been working great in the full Access program.

I'm tempted to just tell them hit okay at the append messages but I
know that will bother me.

Thanks for your help,

Lee
Tim Marshall wrote:

Lee wrote:

Thanks for the reply.

I couldn't remember so I just tried it again and I'm still getting the
error "Too few parameters. Expected 3" And that is using the full
Access program.

I usually construct my SQL dynamically (in VBA) rather than used stored
querydefs. I do recall there were sometimes issues using stored queries
and the execute method. Can you post the SQL of the query(ies) that's
causing this? Unless you figure out or someone comes up with a better
solution, of course. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Albert D. Kallal]

I don't know why the set warnings would fail,I would assume that:

docmd.SetWarnings (false)

DoCmd.OpenQuery ("qryappendtblraceheader")
DoCmd.OpenQuery ("qryappendracedetail")

docmd.SetWarnings (true)

I would think the above should work.

I would actually not try and open a query to run it however (you don't need
to try and open a query to run it..).

If you use the execute method however, then you need to supply the
parameters your self, and you can't use a form expression the sql (I never
understood why people do that..as it just creates a absolute mess anyway).
And, if you queries don't have any parameters, then I not sure what the
problem is.

You can go:

Dim q As DAO.QueryDef
Set q = CurrentDb.QueryDefs("query9")
q.Parameters(0) = "abc"
q.Execute

The above assumes you only have one parameter.

So, if you use the execute method for a query, then you don't have the
expression service to resolve the parameters, but you MUST supply the
parameters in code.

As mentioned, I don't know why the set warnings don't work. (however,
relying on the possibility that some form is open, or not open for the query
to run is not a reliably approach anyway, and then that sql is tied to that
form, and can't be used anywhere else - so, it is not a good programming
practice to use a form expression inside of a query anyway, and it is not
regular sql).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Salad]

Lee wrote:

I tried to do that when I was just working on getting the data to
update another table based on the form and subform and it was beyond
me.

The append query is built upon another query which I use to get
information from the main table (tblhorse) and the race table
(tblraces).

qryhorseandrace

SELECT tblraces.RaceID, tblhorse.Name, tblhorse.Trainer,
tblhorse.Owner, tblraces.Result, tblraces.Time, tblraces.Notes,
tblraces.Jockey, tblraces.Beyer, tblraces.Weight, tblraces.PP,
tblraces.TrackName, tblraces.RaceName, tblraces.RaceDate,
tblraces.Distance, tblraces.RaceType, tblraces.Surface
FROM tblhorse INNER JOIN tblraces ON tblhorse.ID = tblraces.HorseID;

qryappendraceheader

INSERT INTO tblraceheader ( TrackName, RaceName, RaceDate, Distance,
RaceType, Surface )
SELECT DISTINCT qryhorseandrace.TrackName, qryhorseandrace.RaceName,
qryhorseandrace.RaceDate, qryhorseandrace.Distance,
qryhorseandrace.RaceType, qryhorseandrace.Surface
FROM qryhorseandrace LEFT JOIN tblraceheader ON
(qryhorseandrace.RaceDate=tblraceheader.RaceDate) AND
(qryhorseandrace.RaceName=tblraceheader.RaceName) AND
(qryhorseandrace.TrackName=tblraceheader.TrackName )
WHERE
(((qryhorseandrace.TrackName)=Forms!frmhorse!subfr mraces!TrackName) And
((qryhorseandrace.RaceName)=Forms!frmhorse!subfrmr aces!RaceName) And
((qryhorseandrace.RaceDate)=Forms!frmhorse!subfrmr aces!RaceDate) And
((tblraceheader.TrackName) Is Null) And ((tblraceheader.RaceName) Is
Null) And ((tblraceheader.RaceDate) Is Null));

The query to append the details also is based on another query (based
on another query) to find out if the race details have already been
added.

qryfindmissingrace

SELECT qryhorseracedetail.RaceID, qryhorseracedetail.Name,
qryhorseracedetail.Trainer, qryhorseracedetail.Owner,
qryhorseracedetail.Result, qryhorseracedetail.Time,
qryhorseracedetail.Notes, qryhorseracedetail.Jockey,
qryhorseracedetail.Beyer, qryhorseracedetail.Weight,
qryhorseracedetail.PP, qryhorseracedetail.TrackName,
qryhorseracedetail.RaceName, qryhorseracedetail.RaceDate,
qryhorseracedetail.Distance, qryhorseracedetail.RaceType
FROM qryhorseracedetail LEFT JOIN tblracedetails ON
qryhorseracedetail.RaceID = tblracedetails.RaceID
WHERE (((tblracedetails.RaceID) Is Null));

qryhorseracedetail

SELECT tblraceheader.RaceID, qryhorseandrace.Name,
qryhorseandrace.Trainer, qryhorseandrace.Owner, qryhorseandrace.Result,
qryhorseandrace.Time, qryhorseandrace.Notes, qryhorseandrace.Jockey,
qryhorseandrace.Beyer, qryhorseandrace.Weight, qryhorseandrace.PP,
qryhorseandrace.TrackName, qryhorseandrace.RaceName,
qryhorseandrace.RaceDate, qryhorseandrace.Distance,
qryhorseandrace.RaceType
FROM tblraceheader INNER JOIN qryhorseandrace ON
(tblraceheader.RaceDate = qryhorseandrace.RaceDate) AND
(tblraceheader.RaceName = qryhorseandrace.RaceName) AND
(tblraceheader.TrackName = qryhorseandrace.TrackName);

qryappendracedetails

INSERT INTO tblraceheader ( TrackName, RaceName, RaceDate, Distance,
RaceType, Surface )
SELECT DISTINCT qryhorseandrace.TrackName, qryhorseandrace.RaceName,
qryhorseandrace.RaceDate, qryhorseandrace.Distance,
qryhorseandrace.RaceType, qryhorseandrace.Surface
FROM qryhorseandrace LEFT JOIN tblraceheader ON
(qryhorseandrace.RaceDate=tblraceheader.RaceDate) AND
(qryhorseandrace.RaceName=tblraceheader.RaceName) AND
(qryhorseandrace.TrackName=tblraceheader.TrackName )
WHERE
(((qryhorseandrace.TrackName)=Forms!frmhorse!subfr mraces!TrackName) And
((qryhorseandrace.RaceName)=Forms!frmhorse!subfrmr aces!RaceName) And
((qryhorseandrace.RaceDate)=Forms!frmhorse!subfrmr aces!RaceDate) And
((tblraceheader.TrackName) Is Null) And ((tblraceheader.RaceName) Is
Null) And ((tblraceheader.RaceDate) Is Null));
Wow, this looks like a convoluted mess now that I'm seeing it laid out.
It did take me a long time and several efforts to get it working at
all and it's been working great in the full Access program.

I'm tempted to just tell them hit okay at the append messages but I
know that will bother me.

Thanks for your help,

Lee
Tim Marshall wrote:

Lee wrote:

Thanks for the reply.

I couldn't remember so I just tried it again and I'm still getting the
error "Too few parameters. Expected 3" And that is using the full
Access program.

I usually construct my SQL dynamically (in VBA) rather than used stored
querydefs. I do recall there were sometimes issues using stored queries
and the execute method. Can you post the SQL of the query(ies) that's
causing this? Unless you figure out or someone comes up with a better
solution, of course. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

There are 3 references to Forms!frmhorse!subfrmraces. I would assume
that is where your errors are cropping up.

You might want to check out Parameters in help. See if that will assist.

If not, use DAO or ADO to open up the recordset and add the records.
That would be faster, I think, than banging your head against the wall
attempting to make an insert query run.

Another thing to do is enter your values without clicking on the button.
Then open the database and go to the query and try to run it from the
database window. If it errors, it may have the insert at the point the
query chokes.

[John Welch]

> form, and can't be used anywhere else - so, it is not a good programming

practice to use a form expression inside of a query anyway, and it is not
regular sql).
--
Albert D. Kallal (Access MVP)

Albert, I use form expressions all the time in queries that are used as
recordsources for subforms. For example:
Select First, Last, Phone from tblPeople where PersonID =
forms!frmPeople.txtPersonID
I got the idea from the Access Developer's Handbook, so I thought that was
the best way to go.
Are you recommending just leaving with subform without a recordsource at
first, and then when the main form loads (or a textbox is updated or a
button is pushed), setting the subform's recordsource dynamically?

[Salad]

Albert D. Kallal wrote:

I don't know why the set warnings would fail,I would assume that:

docmd.SetWarnings (false)

DoCmd.OpenQuery ("qryappendtblraceheader")
DoCmd.OpenQuery ("qryappendracedetail")

docmd.SetWarnings (true)

I would think the above should work.

I would actually not try and open a query to run it however (you don't need
to try and open a query to run it..).

If you use the execute method however, then you need to supply the
parameters your self, and you can't use a form expression the sql (I never
understood why people do that..as it just creates a absolute mess anyway).
And, if you queries don't have any parameters, then I not sure what the
problem is.

You can go:

Dim q As DAO.QueryDef
Set q = CurrentDb.QueryDefs("query9")
q.Parameters(0) = "abc"
q.Execute

The above assumes you only have one parameter.

So, if you use the execute method for a query, then you don't have the
expression service to resolve the parameters, but you MUST supply the
parameters in code.

As mentioned, I don't know why the set warnings don't work. (however,
relying on the possibility that some form is open, or not open for the query
to run is not a reliably approach anyway, and then that sql is tied to that
form, and can't be used anywhere else - so, it is not a good programming
practice to use a form expression inside of a query anyway, and it is not
regular sql).

Near as I could tell the problem isn't so much with the messages is that
even tho the queries run, they don't append records so the new form
opens up with nothing. I think once he gets the queries working he can
set the warnings to false/true.

[Albert D. Kallal]

Albert, I use form expressions all the time in queries that are used as
recordsources for subforms. For example:
Select First, Last, Phone from tblPeople where PersonID =
forms!frmPeople.txtPersonID
I got the idea from the Access Developer's Handbook, so I thought that was
the best way to go.
It is more of a easy way to go. I can't rally agree with it being the best
way to go. So, easy <> best!

The result of the above sql is now you got this very bad looking sql.
Further, that sql is not compatible with any other sql dialect I seen.
Further, if that form is closed (on purpose, or by accident), then the sql
don't work. And, worse, that query, or sql can ONLY be used when that form
is opened. This is hardly a re-usable approach here. And, if you want to do
data processing, and execute the sql in code, then again you are asking for
trouble. I guess I have the view that you need to figure out the sql, and
THEN send it off to the data engine.
Are you recommending just leaving with subform without a recordsource at
first, and then when the main form loads (or a textbox is updated or a
button is pushed), setting the subform's recordsource dynamically?

Your above need has little, or no relation to needing to using sql with a
forms ref. The above is talking about a datasouce being blank, or not set
for a sub-form. That decision is up to you. But, yes, in the above case, I
would suggest that you put in the sql in code. If you are having to use some
code in this sub-form setting example, then throwing in a extra parameter in
code is really NO MORE work then typing in a very ugly form ref expression
in the query builder. And, as mentioned, that resulting query now married to
that form. Further, if you got query in a sub-form that tied to expression
in the main form, then when do things get executed when you change the forms
value that the query refs? (you have NO IDEA)....

So, to set that sub-form in your example via some button code, I would use

me.SubFormContorl.form.ReocrdSource = "select * from tblCust where id = " &
me!someID

And, if you need the sub-form to be based on a query, then use

me.SubFormContorl.form.ReocrdSource = "select * from myquery where id = " &
me!someID

Keep in mind that you need to correctly form the sql, and thus if the above
someID is text, you need to use quotes: eg:

me.SubFormContorl.form.ReocrdSource = "select * from myquery where id = '" &
me!someID & "'"

The great thing about this is now we have a query that can be use over for
other things (like reports etc.), and we don't have to worry, or care if
some form is open. I mean, you look at some applications, and they got 10
copies of the same query..and the only thing different is forms refs.

What is even worse when you use form refs is what happens when you want the
parameter to be optional? then the expression starts to get really messy
here. So, at the end of the day, those sql form refs are NOT flexibility at
all.

Further, if you use the execute method of a query, then you don't have the
expression service for you in resolving form expressions as parameters
anyway (this seems to be your problem in the original post).

Also, in your/our sub-form example, it sounds like the link child/master
fields could do this job anyway...

In the case for opening a form, or report that needs parameters, then just
pass the "where" clause to that form/report if you need parameters.

docmd.OpenForm "frmCustomers",,,"custid = " & me!someCustID

This again allows you to use clean sql for the form (or report). This is the
whole idea behind the "where" clause for those forms/reports anyway.
Further, when you build nice form prompt screens, allowing optional
parameters is far more easy. Sure, you have to write a bit of code, but the
resulting increase in re-use of the sql, and ease of maintains offsets the
small amount of code.

If you take a look at the following repro prompt screens you will note that
most options are optional. They are optional due to the fact that I open the
reports with a where clause, and don't use sql with reference to forms for
parameters.

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

About the only thing I can say about placing forms references directly in
the sql is they are easy at first, but over time, they are not all that
great. And, to be fair, those expressions can be used in place of having to
write code. So, ones mileage will depend on this. I can't make it a rule and
say don't use forms expressions in sql. This would be mean on my part, and
would suggest that some people have to use code (and, they might not know
code!!). So, this suggestion to avoided forms expressions really is one of
those things that one learns over time, and may NOT apply to everyone.

The only thing I can point out is that there is some real downfalls. And, in
many cases, you might not experience those downfalls.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[John Welch]

Albert thanks so much for taking the time to write such a great response.

"Albert D. Kallal" <ka****@msn.com> wrote in message
news:sJTOe.300938$s54.283228@pd7tw2no...

Albert, I use form expressions all the time in queries that are used as
recordsources for subforms. For example:
Select First, Last, Phone from tblPeople where PersonID =
forms!frmPeople.txtPersonID
I got the idea from the Access Developer's Handbook, so I thought that
was the best way to go.

It is more of a easy way to go. I can't rally agree with it being the best
way to go. So, easy <> best!

The result of the above sql is now you got this very bad looking sql.
Further, that sql is not compatible with any other sql dialect I seen.
Further, if that form is closed (on purpose, or by accident), then the sql
don't work. And, worse, that query, or sql can ONLY be used when that form
is opened. This is hardly a re-usable approach here. And, if you want to
do data processing, and execute the sql in code, then again you are asking
for trouble. I guess I have the view that you need to figure out the sql,
and THEN send it off to the data engine.

Are you recommending just leaving with subform without a recordsource at
first, and then when the main form loads (or a textbox is updated or a
button is pushed), setting the subform's recordsource dynamically?

Your above need has little, or no relation to needing to using sql with a
forms ref. The above is talking about a datasouce being blank, or not set
for a sub-form. That decision is up to you. But, yes, in the above case, I
would suggest that you put in the sql in code. If you are having to use
some code in this sub-form setting example, then throwing in a extra
parameter in code is really NO MORE work then typing in a very ugly form
ref expression in the query builder. And, as mentioned, that resulting
query now married to that form. Further, if you got query in a sub-form
that tied to expression in the main form, then when do things get executed
when you change the forms value that the query refs? (you have NO
IDEA)....

So, to set that sub-form in your example via some button code, I would use

me.SubFormContorl.form.ReocrdSource = "select * from tblCust where id = "
& me!someID

And, if you need the sub-form to be based on a query, then use

me.SubFormContorl.form.ReocrdSource = "select * from myquery where id = "
& me!someID

Keep in mind that you need to correctly form the sql, and thus if the
above someID is text, you need to use quotes: eg:

me.SubFormContorl.form.ReocrdSource = "select * from myquery where id = '"
& me!someID & "'"

The great thing about this is now we have a query that can be use over for
other things (like reports etc.), and we don't have to worry, or care if
some form is open. I mean, you look at some applications, and they got 10
copies of the same query..and the only thing different is forms refs.

What is even worse when you use form refs is what happens when you want
the parameter to be optional? then the expression starts to get really
messy here. So, at the end of the day, those sql form refs are NOT
flexibility at all.

Further, if you use the execute method of a query, then you don't have the
expression service for you in resolving form expressions as parameters
anyway (this seems to be your problem in the original post).

Also, in your/our sub-form example, it sounds like the link child/master
fields could do this job anyway...

In the case for opening a form, or report that needs parameters, then just
pass the "where" clause to that form/report if you need parameters.

docmd.OpenForm "frmCustomers",,,"custid = " & me!someCustID

This again allows you to use clean sql for the form (or report). This is
the whole idea behind the "where" clause for those forms/reports anyway.
Further, when you build nice form prompt screens, allowing optional
parameters is far more easy. Sure, you have to write a bit of code, but
the resulting increase in re-use of the sql, and ease of maintains offsets
the small amount of code.

If you take a look at the following repro prompt screens you will note
that most options are optional. They are optional due to the fact that I
open the reports with a where clause, and don't use sql with reference to
forms for parameters.

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

About the only thing I can say about placing forms references directly in
the sql is they are easy at first, but over time, they are not all that
great. And, to be fair, those expressions can be used in place of having
to write code. So, ones mileage will depend on this. I can't make it a
rule and say don't use forms expressions in sql. This would be mean on my
part, and would suggest that some people have to use code (and, they might
not know code!!). So, this suggestion to avoided forms expressions really
is one of those things that one learns over time, and may NOT apply to
everyone.

The only thing I can point out is that there is some real downfalls. And,
in many cases, you might not experience those downfalls.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Lee]

Thank you all for your replies.

I will do some further research on your points Albert and try to get it
working without using the queries but straight SQL. I have tried that
before and failed but I'll give it another shot. I do understand what
you are saying about limiting the queries to that form but in this
instance they would only be run from the form because the point is to
append information entered in the subform.

I am still puzzled though as to why it works fine in full Access but
fails when I install the runtime version on a PC without Access.
That's the real issue Salad - everything works well until I add the
message blocks and install the runtime on a PC without Access so it's
not as if they don't work at all.

Well, back to the drawing board.

Thanks,

Lee

[Geary Bish]

I thought the setwarnings was a toggle. If the system initially has
setwarnings true and you setwarnings true in you code then setwarnings
is false. Just a thought.

*** Sent via Developersdex http://www.developersdex.com ***

[Rick Brandt]

Geary Bish wrote:

I thought the setwarnings was a toggle. If the system initially has
setwarnings true and you setwarnings true in you code then setwarnings
is false. Just a thought.

*** Sent via Developersdex http://www.developersdex.com ***

No, that is not correct.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com