automation SQL unbound form

[gt]

Hi folks:

I'm using automation to create a new record in an Access table w/ this code:

objAccess.CurrentDb.Execute "INSERT INTO Letters (followed by the fields and
corresponding values). This works fine.

Immediately after that snippet of code I want to open an unbound form
(called cbScreen) in the same db and display the record just created. I've
tried two approaches, shown below, both of which generate errors. Any advice
would be greatly appreciated.

Approach 1:
objAccess.DoCmd.OpenForm "cbscreen", , , "doc" = """ & GBASdoc & """

This approach generates run-time error 2491: The action or method is invalid
because the form or report isn't bound to a table or query. (and I don't want
to bind it)

Approach 2:
strSQL = "SELECT * From letters WHERE (doc) = """ & GBASDoc & """;"
Forms![cbScreen].RecordSource = strSQL

This approach generates the runtime error 462: The remote server machine
does not exist or is unavailable. (it's also generated another error -
Microsoft Access can't find the form...)

Is there a better way to open a specific form and populate it w/ a specific
record - via automation?

Thanks, gt

 

[Frank Stone]

hi,
one way i have used is to dump the record in a temp table
then use dlookup to have the forms text boxs = the temp
table fields.
you would also have to have a delete query to clear the
temp table when done. the temp table is only suppost to
have 1 record in it while you are working with that
record. you have to clear the temp table so you can put
the next record in it.
it works.
regards
Frank

 

[gt]

Thanks for your post, Frank. This approach had not occurred to me. I'll give
it a whirl.

gt

hi,
one way i have used is to dump the record in a temp table
then use dlookup to have the forms text boxs = the temp
table fields.
you would also have to have a delete query to clear the
temp table when done. the temp table is only suppost to
have 1 record in it while you are working with that
record. you have to clear the temp table so you can put
the next record in it.
it works.
regards
Frank

-----Original Message-----
Hi folks:

I'm using automation to create a new record in an Access table w/ this code:

objAccess.CurrentDb.Execute "INSERT INTO Letters (followed by the fields and
corresponding values). This works fine.

Immediately after that snippet of code I want to open an unbound form
(called cbScreen) in the same db and display the record just created. I've
tried two approaches, shown below, both of which generate errors. Any advice
would be greatly appreciated.

Approach 1:
objAccess.DoCmd.OpenForm "cbscreen", , , "doc" = """ & GBASdoc & """

This approach generates run-time error 2491: The action or method is invalid
because the form or report isn't bound to a table or query. (and I don't want
to bind it)

Approach 2:
strSQL = "SELECT * From letters WHERE (doc) = """ & GBASDoc & """;"
Forms![cbScreen].RecordSource = strSQL

This approach generates the runtime error 462: The remote server machine
does not exist or is unavailable. (it's also generated another error -
Microsoft Access can't find the form...)

Is there a better way to open a specific form and populate it w/ a specific
record - via automation?

Thanks, gt

.

 

[Van T. Dinh]

Approach 1 fails because there is nothing (i.e. no data) in an unbound Form
that the "wherecondition" argument (a filtering argument) can be used on.

Approach 2 fails because you haven't opened the Form.

Try opening the Form (unbound with "bound" Controls) first and then
assigning the RecordSource. In effect, you ended up with a bound Form in
operation.

OTOH, there is nothing wrong with the bound Form and use the
"wherecondition" argument. Access will only retrieve the Record that you
want.

An alternative is to use an unbound Form and unbound Controls and simply
fill the values by code. Of course, this means that you will have to use
code to update the values (in the Table) if update is required.

Note that you should use the Idle Method with the argument dbRefreshCache to
make sure the Record is actually added into the Table (rather than still in
the cache) before you open the Form.

HTH
Van T. Dinh
MVP (access)

 

[gt]

Thanks. I'm completely new to Access (and to databases in general), so I
learned a lot from your post. I would like to try your suggestion that I open
the form before issuing the SQL statement, but am not sure how to assign the
recordset to it. I assume something like:

objAccess.DoCmd.OpenForm "cbscreen" (but then how do I assign a recordset in
code?)

Thanks
gt

Approach 1 fails because there is nothing (i.e. no data) in an unbound Form
that the "wherecondition" argument (a filtering argument) can be used on.

Approach 2 fails because you haven't opened the Form.

Try opening the Form (unbound with "bound" Controls) first and then
assigning the RecordSource. In effect, you ended up with a bound Form in
operation.

OTOH, there is nothing wrong with the bound Form and use the
"wherecondition" argument. Access will only retrieve the Record that you
want.

An alternative is to use an unbound Form and unbound Controls and simply
fill the values by code. Of course, this means that you will have to use
code to update the values (in the Table) if update is required.

Note that you should use the Idle Method with the argument dbRefreshCache to
make sure the Record is actually added into the Table (rather than still in
the cache) before you open the Form.

HTH
Van T. Dinh
MVP (access)

Hi folks:

I'm using automation to create a new record in an Access table w/ this code:

objAccess.CurrentDb.Execute "INSERT INTO Letters (followed by the fields and
corresponding values). This works fine.

Immediately after that snippet of code I want to open an unbound form
(called cbScreen) in the same db and display the record just created. I've
tried two approaches, shown below, both of which generate errors. Any advice
would be greatly appreciated.

Approach 1:
objAccess.DoCmd.OpenForm "cbscreen", , , "doc" = """ & GBASdoc & """

This approach generates run-time error 2491: The action or method is invalid
because the form or report isn't bound to a table or query. (and I don't want
to bind it)

Approach 2:
strSQL = "SELECT * From letters WHERE (doc) = """ & GBASDoc & """;"
Forms![cbScreen].RecordSource = strSQL

This approach generates the runtime error 462: The remote server machine
does not exist or is unavailable. (it's also generated another error -
Microsoft Access can't find the form...)

Is there a better way to open a specific form and populate it w/ a specific
record - via automation?

Thanks, gt

 

[Van T. Dinh]

You have to code the Form_Open Event Procedure with something like:

Me.RecordSource = "SELECT ...."