Sub procedures: need to set up a few

[Guest]

Hello

My quest continues, but I have a few more detailed questions

I actually wonder if there is good book for this, but here it is, I would like to find some code for the following

1. On opening the file, to delete a table via a macro
2. On opening the file, to run a query

In excel this was so easy, it was broken down by sheets, here only the code for the forms can be seen

Is there a good indexed website out there that would break down code by objects like tables, queries and forms, for the most obvious tasks such as "if the file opens I'd like it do run this query first"? It's possible to set up the startup option for the forms, but not queries.

Thanks guys

pepe

 

[Dirk Goldgar]

Hello:

My quest continues, but I have a few more detailed questions.

I actually wonder if there is good book for this, but here it is, I
would like to find some code for the following:

1. On opening the file, to delete a table via a macro.
2. On opening the file, to run a query.

In excel this was so easy, it was broken down by sheets, here only
the code for the forms can be seen.

I don't follow you. You can create standard modules on the Modules tab
of the database, write Functions and Sub procedures inside them, and
call those procedures from anywhere.

Is there a good indexed website out there that would break down code
by objects like tables, queries and forms, for the most obvious tasks
such as "if the file opens I'd like it do run this query first"?

The Access Web, www.mvps.org/access , is a good place to start.

It's
possible to set up the startup option for the forms, but not queries.

One approach is to use a startup form and call procedures from that
form. Another is to have an Autoexec macro that uses the RunCode action
to execute a function (not a sub) that you've written in a standard
module; the function might be something like this:

Function StartUp()

' Delete table "tblX", ignoring error if it
' doesn't exist.

On Error Resume Next
DoCmd.DeleteObject acTable, "tblX"
If Err.Number <> 7874 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If

' Open query "qryY".
DoCmd.OpenQuery "qryY"

End Function

 

[Guest]

Dirk
Thanks, I implemented only the query part of it for now and so far so good. My question is why does one have to use a function and not a sub procedure to call a query out.

pep

----- Dirk Goldgar wrote: ----

Hello
would like to find some code for the following
2. On opening the file, to run a query
the code for the forms can be seen

I don't follow you. You can create standard modules on the Modules ta
of the database, write Functions and Sub procedures inside them, an
call those procedures from anywhere

Is there a good indexed website out there that would break down cod
by objects like tables, queries and forms, for the most obvious task
such as "if the file opens I'd like it do run this query first"

The Access Web, www.mvps.org/access , is a good place to start

It'
possible to set up the startup option for the forms, but not queries

One approach is to use a startup form and call procedures from tha
form. Another is to have an Autoexec macro that uses the RunCode actio
to execute a function (not a sub) that you've written in a standar
module; the function might be something like this

Function StartUp(

' Delete table "tblX", ignoring error if i
' doesn't exist

On Error Resume Nex
DoCmd.DeleteObject acTable, "tblX
If Err.Number <> 7874 The
MsgBox Err.Description, vbExclamation, "Error " & Err.Numbe
End I

' Open query "qryY"
DoCmd.OpenQuery "qryY

End Functio

 

[Dirk Goldgar]

Dirk:
Thanks, I implemented only the query part of it for now and so far so
good. My question is why does one have to use a function and not a
sub procedure to call a query out.

It's not that you can't open a query from a Sub -- that's not true.
It's that you can't use the RunCode macro action to call a Sub
procedure; only a Function.

 

[Guest]

I see. Here's another if I may - I think I'll try to learn how to fish rather than ask for a meal

The books, on the site you recommended, will become a part of my library one way or another; however..

When I got my first VB.Net CD, some months ago when I took a VB.Net course, the package included about 4 posters with lists of controls and more or less what they did. They were huge but useful

Is there something like that applicable to Access-specifi controls, a guide of sorts, that I can download, or buy

The thing is that I can read and assemble basic code (and I'm a bit rusty as you probably noticed), I just usually have a hell of a time finding the right controls.

Thanks,
pepe

----- Dirk Goldgar wrote: ----

Dirk
Thanks, I implemented only the query part of it for now and so far s
good. My question is why does one have to use a function and not
sub procedure to call a query out

It's not that you can't open a query from a Sub -- that's not true
It's that you can't use the RunCode macro action to call a Su
procedure; only a Function

 

[Dirk Goldgar]

I see. Here's another if I may - I think I'll try to learn how to
fish rather than ask for a meal.

The books, on the site you recommended, will become a part of my
library one way or another; however...

When I got my first VB.Net CD, some months ago when I took a VB.Net
course, the package included about 4 posters with lists of controls
and more or less what they did. They were huge but useful.

Is there something like that applicable to Access-specifi controls, a
guide of sorts, that I can download, or buy?

I wish there were, but I don't know of any. However, if you poke around
in the help file in the topic "Programming in Visual Basic" ->
"Microsoft Access Visual Basic Reference", you'll find some
object-library charts, such as the one called "Microsoft Access Object
Model". I don't see a handy chart showing all the controls with links
to their properties, though. As far as I can tell you have to look up
each control in that section of the help file.

Maybe one of the various books on Access has such a chart.

The thing is that I can read and assemble basic code (and I'm a bit
rusty as you probably noticed), I just usually have a hell of a time
finding the right controls.

FWIW, finding things in the help (ever since Access 2000) is hard, but
the content of the help is pretty good. If you read the help files as a
text, you can learn a lot.

 

[Guest]

Dirk

FWIW? not sure what you mean

In regards to the charts, I actually noticed that during the day today - I'm talking about the help files and their usefulness which you mentioned. I looked up some controls in the VB editor, then looked up the name of the control in the Help files, and sure enough got the code I needed. Consequently, I completely see what you are saying.

I'm beyond the the AutoExec file now, all the way to importing tables using code. Thank you

Say, if I want a query to avoid null values in a field can I just specify in the criteri

<>

and get the result. It seems to work but I'm interested in a second opinion to see if you've ever had a problem using that.

Thanks for all the help; this community is absolutely invaluable

pep

----- Dirk Goldgar wrote: ----

Is there something like that applicable to Access-specifi controls,
guide of sorts, that I can download, or buy

I wish there were, but I don't know of any. However, if you poke aroun
in the help file in the topic "Programming in Visual Basic" -
"Microsoft Access Visual Basic Reference", you'll find som
object-library charts, such as the one called "Microsoft Access Objec
Model". I don't see a handy chart showing all the controls with link
to their properties, though. As far as I can tell you have to look u
each control in that section of the help file

Maybe one of the various books on Access has such a chart

The thing is that I can read and assemble basic code (and I'm a bi
rusty as you probably noticed), I just usually have a hell of a tim
finding the right controls

FWIW, finding things in the help (ever since Access 2000) is hard, bu
the content of the help is pretty good. If you read the help files as
text, you can learn a lot

 

[Dirk Goldgar]

Dirk:

FWIW? not sure what you mean.

Sorry. "FWIW" = "For What It's Worth".

I'm beyond the the AutoExec file now, all the way to importing tables
using code. Thank you.

You're welcome.

Say, if I want a query to avoid null values in a field can I just
specify in the criteria

<>0

and get the result. It seems to work but I'm interested in a second
opinion to see if you've ever had a problem using that.

You're talking about criteria in a query? That's not a good way to
exclude nulls, because it also excludes zeros. If you want to exclude
only null values, use this criterion instead:

Is Not Null

 

[Guest]

Dirk, I have another question about filling in the contents of a cell automatically

Let's say I have two fields in a tabl

FieldA Field

Let's say FieldA is fully populated, while FieldB has only sporradic entries. Is there a way to execute an IF THEN procedure that would enable FieldB to default back to FieldA's value, if it (FieldB) itself is Null

Thank
pep


----- Dirk Goldgar wrote: ----

Say, if I want a query to avoid null values in a field can I jus
specify in the criteri
opinion to see if you've ever had a problem using that

You're talking about criteria in a query? That's not a good way t
exclude nulls, because it also excludes zeros. If you want to exclud
only null values, use this criterion instead

Is Not Nul

 

[John Spencer (MVP)]

Pardon me for jumping in.

No, there is no way to do this in a TABLE. You can do it in a query using the
NZ function.

Nz(FieldB, FieldA)
will return the value of FieldA whenever FieldB is null. It does NOT store the
actual value of FieldA in FieldB.

 

[Guest]

John:

Thank you, that's exactly the tip that I needed. I had no idea these functions were available but per Dirk's suggestion I've begun the process of reference acquisition.

You guys are awesome

Pep


----- John Spencer (MVP) wrote: ----

Pardon me for jumping in

No, there is no way to do this in a TABLE. You can do it in a query using th
NZ function

Nz(FieldB, FieldA)
will return the value of FieldA whenever FieldB is null. It does NOT store th
actual value of FieldA in FieldB

pepenacho wrote

 

[Guest]

John & Dirk

I actually ended up using this instead, in a qry, but the principle is the sam

Expr: IIf(IsNull([Unit Type 2]),[Unit Type 1],[Unit Type 2]

The reference materials (mostly Access programming) came in as well, so I'm excited and looking forward to using them

pep

----- John Spencer (MVP) wrote: ----

Pardon me for jumping in

No, there is no way to do this in a TABLE. You can do it in a query using th
NZ function

Nz(FieldB, FieldA)
will return the value of FieldA whenever FieldB is null. It does NOT store th
actual value of FieldA in FieldB