Opening a form based on a query

[souchie40]

This problem is driving me crackers and I would appreciate any suggestions
and help that anybody supplies. My proble is that my query is based on two
tables one which as a multiple number of combo boxes to populate repetative
fields, now each combo box ROWSOURCE is based on SELECT DISTINCTROW SQL
statement, each time I open the form to populate the data I get the foloowing
error message: -

The Microsoft Jet database engine cannot find the input table or query
SELECT DISTINCTROW[Planned Procurement].[ID], [Planned Procurement].[Ship
Class] FROM[Planned Procurement] Make sure it exists and that its name is
spelled correctly.

All the record sets and varibles in the control forms are set to read
identical values.
If I click ok the form opens with missing data from the completed records,
if I then switch to design view and switch back as if by magic my data comes
back and I can carry on creating new records wich is fine for me but I don't
want anyone having access to the DB window owhen I roll this out.

Many Thanks in advance

 

[Brian]

Not sure where it is coming from, but Access thinks the NAME of your query
(not its content) is SELECT DISTINCTROW[Planned Procurement].[ID], [Planned
Procurement].[Ship
Class] FROM[Planned Procurement].

Where exactly is the above SQL sitting? In the RecordSource of your form? In
the RowSource of a control?

Just as a test, try pasting your SQL statement into a query and running the
query directly first. Does it provide the correct results?

 

[souchie40]

Not sure where it is coming from, but Access thinks the NAME of your query
(not its content) is SELECT DISTINCTROW[Planned Procurement].[ID], [Planned
Procurement].[Ship
Class] FROM[Planned Procurement].

Where exactly is the above SQL sitting? In the RecordSource of your form? In
the RowSource of a control?

In the RowSource of the control

Just as a test, try pasting your SQL statement into a query and running the
query directly first. Does it provide the correct results?

If I do this then I get the expected results

This problem is driving me crackers and I would appreciate any suggestions
and help that anybody supplies. My proble is that my query is based on two
tables one which as a multiple number of combo boxes to populate repetative
fields, now each combo box ROWSOURCE is based on SELECT DISTINCTROW SQL
statement, each time I open the form to populate the data I get the foloowing
error message: -

The Microsoft Jet database engine cannot find the input table or query
SELECT DISTINCTROW[Planned Procurement].[ID], [Planned Procurement].[Ship
Class] FROM[Planned Procurement] Make sure it exists and that its name is
spelled correctly.

All the record sets and varibles in the control forms are set to read
identical values.
If I click ok the form opens with missing data from the completed records,
if I then switch to design view and switch back as if by magic my data comes
back and I can carry on creating new records wich is fine for me but I don't
want anyone having access to the DB window owhen I roll this out.

Many Thanks in advance

 

[Brian]

Just a suggestion - more as a test than as a solution. Try saving & testing
your SQL statement as a query (which you just did in the prior test) and then
putting the name of the new query in the RowSource instead of the SQL
statement itself. Do you still get the same error?

If so, try simply removing the RowSource from that control & opening the
form once. Does the error still appear?

Not sure where it is coming from, but Access thinks the NAME of your query
(not its content) is SELECT DISTINCTROW[Planned Procurement].[ID], [Planned
Procurement].[Ship
Class] FROM[Planned Procurement].

Where exactly is the above SQL sitting? In the RecordSource of your form? In
the RowSource of a control?

In the RowSource of the control

Just as a test, try pasting your SQL statement into a query and running the
query directly first. Does it provide the correct results?

If I do this then I get the expected results

This problem is driving me crackers and I would appreciate any suggestions
and help that anybody supplies. My proble is that my query is based on two
tables one which as a multiple number of combo boxes to populate repetative
fields, now each combo box ROWSOURCE is based on SELECT DISTINCTROW SQL
statement, each time I open the form to populate the data I get the foloowing
error message: -

The Microsoft Jet database engine cannot find the input table or query
SELECT DISTINCTROW[Planned Procurement].[ID], [Planned Procurement].[Ship
Class] FROM[Planned Procurement] Make sure it exists and that its name is
spelled correctly.

All the record sets and varibles in the control forms are set to read
identical values.
If I click ok the form opens with missing data from the completed records,
if I then switch to design view and switch back as if by magic my data comes
back and I can carry on creating new records wich is fine for me but I don't
want anyone having access to the DB window owhen I roll this out.

Many Thanks in advance

 

[souchie40]

Just a suggestion - more as a test than as a solution. Try saving & testing
your SQL statement as a query (which you just did in the prior test) and then
putting the name of the new query in the RowSource instead of the SQL
statement itself. Do you still get the same error?

Did this and the error goes away, the only problem no is that all my other
combo boxes fail to display the underlying data unless I go into design mode
first.

If so, try simply removing the RowSource from that control & opening the
form once. Does the error still appear?

Not sure where it is coming from, but Access thinks the NAME of your query
(not its content) is SELECT DISTINCTROW[Planned Procurement].[ID], [Planned
Procurement].[Ship
Class] FROM[Planned Procurement].

Where exactly is the above SQL sitting? In the RecordSource of your form? In
the RowSource of a control?

In the RowSource of the control

Just as a test, try pasting your SQL statement into a query and running the
query directly first. Does it provide the correct results?

If I do this then I get the expected results

:

This problem is driving me crackers and I would appreciate any suggestions
and help that anybody supplies. My proble is that my query is based on two
tables one which as a multiple number of combo boxes to populate repetative
fields, now each combo box ROWSOURCE is based on SELECT DISTINCTROW SQL
statement, each time I open the form to populate the data I get the foloowing
error message: -

The Microsoft Jet database engine cannot find the input table or query
SELECT DISTINCTROW[Planned Procurement].[ID], [Planned Procurement].[Ship
Class] FROM[Planned Procurement] Make sure it exists and that its name is
spelled correctly.

All the record sets and varibles in the control forms are set to read
identical values.
If I click ok the form opens with missing data from the completed records,
if I then switch to design view and switch back as if by magic my data comes
back and I can carry on creating new records wich is fine for me but I don't
want anyone having access to the DB window owhen I roll this out.

Many Thanks in advance

 

[souchie40]

Thank you for your time and patience Brian, by some luck I've manged to
resolve the problem it seems that I needed to put a space after 'DISTINCTROW'
and 'FROM' or at least this now seems to work so once again thank you for
your help

Just a suggestion - more as a test than as a solution. Try saving & testing
your SQL statement as a query (which you just did in the prior test) and then
putting the name of the new query in the RowSource instead of the SQL
statement itself. Do you still get the same error?

If so, try simply removing the RowSource from that control & opening the
form once. Does the error still appear?

Not sure where it is coming from, but Access thinks the NAME of your query
(not its content) is SELECT DISTINCTROW[Planned Procurement].[ID], [Planned
Procurement].[Ship
Class] FROM[Planned Procurement].

Where exactly is the above SQL sitting? In the RecordSource of your form? In
the RowSource of a control?

In the RowSource of the control

Just as a test, try pasting your SQL statement into a query and running the
query directly first. Does it provide the correct results?

If I do this then I get the expected results

:

This problem is driving me crackers and I would appreciate any suggestions
and help that anybody supplies. My proble is that my query is based on two
tables one which as a multiple number of combo boxes to populate repetative
fields, now each combo box ROWSOURCE is based on SELECT DISTINCTROW SQL
statement, each time I open the form to populate the data I get the foloowing
error message: -

The Microsoft Jet database engine cannot find the input table or query
SELECT DISTINCTROW[Planned Procurement].[ID], [Planned Procurement].[Ship
Class] FROM[Planned Procurement] Make sure it exists and that its name is
spelled correctly.

All the record sets and varibles in the control forms are set to read
identical values.
If I click ok the form opens with missing data from the completed records,
if I then switch to design view and switch back as if by magic my data comes
back and I can carry on creating new records wich is fine for me but I don't
want anyone having access to the DB window owhen I roll this out.

Many Thanks in advance

 

[Brian]

Yes. That makes sense, and sometimes it is hard to focus on those kinds of
details in the forum. The interesting thing is that it worked when you cut &
pasted it into a standalone query. Perhaps Access automatically inserted the
spaces for you when you put it into the query.

Thank you for your time and patience Brian, by some luck I've manged to
resolve the problem it seems that I needed to put a space after 'DISTINCTROW'
and 'FROM' or at least this now seems to work so once again thank you for
your help

Just a suggestion - more as a test than as a solution. Try saving & testing
your SQL statement as a query (which you just did in the prior test) and then
putting the name of the new query in the RowSource instead of the SQL
statement itself. Do you still get the same error?

If so, try simply removing the RowSource from that control & opening the
form once. Does the error still appear?

:

Not sure where it is coming from, but Access thinks the NAME of your query
(not its content) is SELECT DISTINCTROW[Planned Procurement].[ID], [Planned
Procurement].[Ship
Class] FROM[Planned Procurement].

Where exactly is the above SQL sitting? In the RecordSource of your form? In
the RowSource of a control?

In the RowSource of the control

Just as a test, try pasting your SQL statement into a query and running the
query directly first. Does it provide the correct results?

If I do this then I get the expected results

:

This problem is driving me crackers and I would appreciate any suggestions
and help that anybody supplies. My proble is that my query is based on two
tables one which as a multiple number of combo boxes to populate repetative
fields, now each combo box ROWSOURCE is based on SELECT DISTINCTROW SQL
statement, each time I open the form to populate the data I get the foloowing
error message: -

The Microsoft Jet database engine cannot find the input table or query
SELECT DISTINCTROW[Planned Procurement].[ID], [Planned Procurement].[Ship
Class] FROM[Planned Procurement] Make sure it exists and that its name is
spelled correctly.

All the record sets and varibles in the control forms are set to read
identical values.
If I click ok the form opens with missing data from the completed records,
if I then switch to design view and switch back as if by magic my data comes
back and I can carry on creating new records wich is fine for me but I don't
want anyone having access to the DB window owhen I roll this out.

Many Thanks in advance

 

[souchie40]

When I want back to an earlier version of my Database and done the same again
Access did put in the spaces within the statement. I'll have to be careful on
that one in future

Yes. That makes sense, and sometimes it is hard to focus on those kinds of
details in the forum. The interesting thing is that it worked when you cut &
pasted it into a standalone query. Perhaps Access automatically inserted the
spaces for you when you put it into the query.

Thank you for your time and patience Brian, by some luck I've manged to
resolve the problem it seems that I needed to put a space after 'DISTINCTROW'
and 'FROM' or at least this now seems to work so once again thank you for
your help

Just a suggestion - more as a test than as a solution. Try saving & testing
your SQL statement as a query (which you just did in the prior test) and then
putting the name of the new query in the RowSource instead of the SQL
statement itself. Do you still get the same error?

If so, try simply removing the RowSource from that control & opening the
form once. Does the error still appear?

:



:

Not sure where it is coming from, but Access thinks the NAME of your query
(not its content) is SELECT DISTINCTROW[Planned Procurement].[ID], [Planned
Procurement].[Ship
Class] FROM[Planned Procurement].

Where exactly is the above SQL sitting? In the RecordSource of your form? In
the RowSource of a control?

In the RowSource of the control

Just as a test, try pasting your SQL statement into a query and running the
query directly first. Does it provide the correct results?

If I do this then I get the expected results

:

This problem is driving me crackers and I would appreciate any suggestions
and help that anybody supplies. My proble is that my query is based on two
tables one which as a multiple number of combo boxes to populate repetative
fields, now each combo box ROWSOURCE is based on SELECT DISTINCTROW SQL
statement, each time I open the form to populate the data I get the foloowing
error message: -

The Microsoft Jet database engine cannot find the input table or query
SELECT DISTINCTROW[Planned Procurement].[ID], [Planned Procurement].[Ship
Class] FROM[Planned Procurement] Make sure it exists and that its name is
spelled correctly.

All the record sets and varibles in the control forms are set to read
identical values.
If I click ok the form opens with missing data from the completed records,
if I then switch to design view and switch back as if by magic my data comes
back and I can carry on creating new records wich is fine for me but I don't
want anyone having access to the DB window owhen I roll this out.

Many Thanks in advance