Newbie questions

[Paul E. Schoen]

These will probably be simple stuff and I thought it best to just list them
as I need help.

(1) First, I finally got this to work:

Private Sub btNext_Click()
DoCmd.GoToRecord , , acNext
Rem Recordset.Move 1
Form_HorseInfo.Refresh
End Sub

The Recordset.Move seems to do the same. So is one method better than the
other?

I had problems when I tried:
DoCmd.GoToRecord (, , acNext)
which gave a compiler error expects "="

I'm just not used to function parameters not in parentheses.

(2) But now I have a minor problem. My form has buttons to advance to the
next and previous records as shown above. I also have an event handler:

Private Sub Form_Current()
tbAge = 0
If tbDOB <> "" And tbEditDate <> "" Then
tbAge = Format(Int(tbEditDate - tbDOB) / 365.25, "###.00")
End If
If Me.NewRecord Then
btNext.Enabled = False
Else
btNext.Enabled = True
End If
If Me.CurrentRecord = 1 Then
btPrevious.Enabled = False
Else
btPrevious.Enabled = True
End If
If Me.CurrentRecord = Me.Recordset.RecordCount Then
btNext.Enabled = False
Else
btNext.Enabled = True
End If
End Sub

This works fine, except that I need to click the button twice for it to
advance to the next record. It seems like the Form.Current event gets
swallowed by its handler and not passed on to the button click handler (or
vice versa). If I remove the Form.Current handler the records move as
desired, but I get errors if I click past the ends, which is why I added
the button disable. Any ideas?

(3) Finally, I would like to have this database open without the GUI menu
items. I have an autoexec to a simple main form which then launches my
other forms and reports, but I want to make it simple for the end user.
This might have something to do with loading ribbons with XML markup, but
that sounds too complicated. I just want the forms to display and perhaps
require a special way to run the program in the usual developer mode. I'm
sure this is simple but I have not found how yet. Probably as soon as I
click send to this post. But by the time I get answers I'll probably have
more questions.

Thanks,

Paul

 

[Allen Browne]

Hi Paul. Responses in-line. Hope it's not too much info.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

These will probably be simple stuff and I thought it best to just list
them as I need help.

(1) First, I finally got this to work:

Private Sub btNext_Click()
DoCmd.GoToRecord , , acNext
Rem Recordset.Move 1
Form_HorseInfo.Refresh
End Sub

The Recordset.Move seems to do the same. So is one method better than the
other?

Recordset was introduced in A2000; the GoToRecord is more common as it works
with older records.

Best way to learn anything, is to try out what works and what does not. For
example, you might test:

a) What if the current record is not yet saved?
What happens if it can't be saved (e.g. a required field is blank)?

b) What if there is no next record?
Or if you are editing a new record?

c) Does this work in a subform?
Where does the button have to be?
So what views does it work in? (E.g. Datasheet would need a toolbar button)

d) Is the Next button enabled property right when the form is first opened?
Even on a form bound to a query (or attached table) with lots of records
(10k+)

e) Can you hold this button down to spin through records (as you can with
the built-in navigation buttons, at least up to A2003)? Does the button's
AutoRepeat property work?

f) What happens if one of these buttons has focus, and the code tries to
disable it? (You will need error handling.)

g) If the buttons are in the Form Header or Form Footer section on a form
where there are no records (e.g. filtered) and no new records can be added
(e.g. AllowAdditions is No), what happens when you click your buttons?

I had problems when I tried:
DoCmd.GoToRecord (, , acNext)
which gave a compiler error expects "="

I'm just not used to function parameters not in parentheses.

DoCmd is not actually a function. It's a member of the Access library, that
exposes the object model in Access.

In any case, VBA has two kinds of procedures that are not handled
consistently:
- functions, that generally do have parentheses;
- subs, that generally do not.

For example, you could create these 2 procedures:
Function DoSomething(frm As Form)
...
End Function
Sub DoSomethingElse frm As Form
...
End Sub

To help avoid the inconsistencies, you can use the optional Call statement
so that the behavior is the same (assuming you don't care about the return
value of the function, e.g.:
Call DoSomething(Me)

(2) But now I have a minor problem. My form has buttons to advance to the
next and previous records as shown above. I also have an event handler:

Private Sub Form_Current()
tbAge = 0

What is tbAge? If it's a text box bound to a field, you are dirtying the
record as soon as you visit it. That's that cause of your problem, and
something you want to avoid at all costs.

If it's an unbound text box, that should not cause the problem.

If it's a variable, always use Option Explicit, and declare it in this
procedure if possible.

If tbDOB <> "" And tbEditDate <> "" Then
tbAge = Format(Int(tbEditDate - tbDOB) / 365.25, "###.00")
End If

You're testing for a zero-length string, but not for Null? They are not the
same.

In this context, tblAge won't be set for Nulls either, but I'm just checking
that you do know that any IF has 3 possible outcomes to consider. Details in
Error #6 of:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

If Me.NewRecord Then
btNext.Enabled = False
Else
btNext.Enabled = True
End If
If Me.CurrentRecord = 1 Then
btPrevious.Enabled = False
Else
btPrevious.Enabled = True
End If
If Me.CurrentRecord = Me.Recordset.RecordCount Then
btNext.Enabled = False
Else
btNext.Enabled = True
End If
End Sub

This works fine, except that I need to click the button twice for it to
advance to the next record. It seems like the Form.Current event gets
swallowed by its handler and not passed on to the button click handler (or
vice versa). If I remove the Form.Current handler the records move as
desired, but I get errors if I click past the ends, which is why I added
the button disable. Any ideas?

Suggestion above re dirtying the form via tbAge would be the first suspect.

(3) Finally, I would like to have this database open without the GUI menu
items. I have an autoexec to a simple main form which then launches my
other forms and reports, but I want to make it simple for the end user.
This might have something to do with loading ribbons with XML markup, but
that sounds too complicated. I just want the forms to display and perhaps
require a special way to run the program in the usual developer mode. I'm
sure this is simple but I have not found how yet. Probably as soon as I
click send to this post. But by the time I get answers I'll probably have
more questions.

Perhaps I should let someone else answer this. I take it you are using
A2007.

 

[Paul E. Schoen]

(3) Finally, I would like to have this database open without the GUI menu
items. I have an autoexec to a simple main form which then launches my
other forms and reports, but I want to make it simple for the end user.
This might have something to do with loading ribbons with XML markup, but
that sounds too complicated. I just want the forms to display and perhaps
require a special way to run the program in the usual developer mode. I'm
sure this is simple but I have not found how yet. Probably as soon as I
click send to this post. But by the time I get answers I'll probably have
more questions.

Yup, I did a search on Dogpile and got this:
http://www.tek-tips.com/viewthread.cfm?qid=1538459&page=1

All you have to do is rename the file from accdb to accdr!

Now if I enter "accdr" or "runtime" into the help search, I find what I am
looking for. But that's cheating. This was a lot easier in previous
versions.

I tried following the instructions to set up a table of ribbons and then
exit and restart, but the blank table I created went missing. I also tried
turning off the navigation pane as instructed and only the object browser
on the left disappeared.

There are also a bunch of command line switches, but changing the extension
works perfectly and is the best way I know to distribute the program to my
customer in a way that is less intimidating and confusing and less likely
for them to screw up.

Paul

 

[Albert D. Kallal]

These will probably be simple stuff and I thought it best to just list
them as I need help.

(1) First, I finally got this to work:

Private Sub btNext_Click()
DoCmd.GoToRecord , , acNext
Form_HorseInfo.Refresh
End Sub

I strongly recommend that you do NOT use the forms "base" class object as
you have above.

Furthermore, you should not (do not) need the refresh, as the form does that
for you when you move. This is what is likely causing your forms on-current
to re-fire more then once.

The correct way to reference the form in the above is:

forms!HorseInfo.Refresh
or
forms!("HorseInfo").Refresh
or
me.Refresh <- this is most preferred


Note that the 2nd way allows you to use a variable for the form name

dim strF as string
strF = "HorseInfo"
forms(strF).Refresh

The last example uses "me". This is preference since "me" is the current
running instance of the form. That means:

If we copy the form or re-name the form, the code will not break
We can freely copy the code between different forms and have "more" code
re-use

You never want to reference the forms "base class object" of Form_FormName

there are MANY reasons for this:

If the form is not opened, then simply referencing the Form_ForName will
cause the form to launch, and all of the form "open", "load", "on-current"
etc. will fire. This is not expected behaviors.

When referencing Form_Forname, the form does NOT get added to the forms()
collection.

If you have multiple instances of the form launched, then what instance
of the form are you referencing?

In access 97 you were able to reference base class objects you create and
forms (NB: any form with code in it is actually a access "class object").
Note that if you create a form WITHOUT any code in it, then Form_FormName is
NOT legal syntax....

the records move as

desired, but I get errors if I click past the ends, which is why I added
the button disable. Any ideas?

Well as a general rule, I would just enable he built in buttons and be done
with this issue. However, here the code I use:

On Error Resume Next
DoCmd.GoToRecord acActiveDataObject, , acNext

I would like to have this database open without the GUI menu

items.

I have a working sample here that does this here:
http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm

(but, I see you found the runtime re-name trick). I going to assume you
split your database. And, then you create a accDE, then rename that as
accDR)

If you not split your database, then I explain this concept here from a
developers point of view:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Paul E. Schoen]

Hi Paul. Responses in-line. Hope it's not too much info.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



Recordset was introduced in A2000; the GoToRecord is more common as it
works with older records.

Best way to learn anything, is to try out what works and what does not.
For example, you might test:

a) What if the current record is not yet saved?
What happens if it can't be saved (e.g. a required field is blank)?

b) What if there is no next record?
Or if you are editing a new record?

c) Does this work in a subform?
Where does the button have to be?
So what views does it work in? (E.g. Datasheet would need a toolbar
button)

d) Is the Next button enabled property right when the form is first
opened? Even on a form bound to a query (or attached table) with lots of
records (10k+)

e) Can you hold this button down to spin through records (as you can with
the built-in navigation buttons, at least up to A2003)? Does the
button's AutoRepeat property work?

f) What happens if one of these buttons has focus, and the code tries to
disable it? (You will need error handling.)

g) If the buttons are in the Form Header or Form Footer section on a form
where there are no records (e.g. filtered) and no new records can be
added (e.g. AllowAdditions is No), what happens when you click your
buttons?

Thanks for the suggestions. I need to test those conditions.

DoCmd is not actually a function. It's a member of the Access library,
that exposes the object model in Access.

In any case, VBA has two kinds of procedures that are not handled
consistently:
- functions, that generally do have parentheses;
- subs, that generally do not.

For example, you could create these 2 procedures:
Function DoSomething(frm As Form)
...
End Function
Sub DoSomethingElse frm As Form
...
End Sub

To help avoid the inconsistencies, you can use the optional Call
statement so that the behavior is the same (assuming you don't care about
the return value of the function, e.g.:
Call DoSomething(Me)


What is tbAge? If it's a text box bound to a field, you are dirtying the
record as soon as you visit it. That's that cause of your problem, and
something you want to avoid at all costs.

If it's an unbound text box, that should not cause the problem.

Yes, it is unbound text box.

If it's a variable, always use Option Explicit, and declare it in this
procedure if possible.


You're testing for a zero-length string, but not for Null? They are not
the same.

In this context, tblAge won't be set for Nulls either, but I'm just
checking that you do know that any IF has 3 possible outcomes to
consider. Details in Error #6 of:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

I will need to check that out. Thanks.

Suggestion above re dirtying the form via tbAge would be the first
suspect.

tbAge is an unbound text box that is calculated every time the form becomes
current. Now that I think about it, I should use now() rather than
tbEditDate. But I found the problem, which was the fact that I was
resetting EditDate upon FormDirty which was recursive, until I used Day().
Otherwise it would detect a change of one second and recalculate:

Private Sub Form_Dirty(Cancel As Integer)
If Day(EditDate) <> Day(Now) Then
EditDate = Now
End If
End Sub

Thanks for the help. I have a lot to learn. Thankfully this is a small
application for a horse rescue farm and not a commercial app that will need
to be distributed in quantity. But if it works well enough, it might be
helpful for other horse rescues, and other similar charitable organizations
like dog and cat rescues. It lists information specific to each horse, and
then also links to medical records such as vet visits, hoof repair, etc.

Paul