Access-Forms-VB links

[gbostock]

After years in software development/database programming mostly on
Unix/Oracle I'm tasked to change some Access forms to use redesigned
tables. I'm having trouble with the relationship between forms and VB.
For example I'm looking at some VB code that frequently references the
Me class which I assume to be the default reference to the current
(form) class (like this in C++), but I can't see how/where the class
is actually defined. Can someone please provide some links that
provide concise explanations of the relationship between Access and
VB. My googling has only turned up pages devoted to Access or VB but
not both in context to each other.

Thanks.

 

[Guest]

I don't have a link for you, but I think I can explain it so you will get the
idea.
You are correct about Me. It is a reference to the form the code is in. It
can also be a report.

All VBA code is either a Sub or a Function. In reality, there is little
difference, Mainly a Function returns a value and a Sub does not. Either
will accept arguments.
All Subs and Functions are collected in Modules. There are basicly three
kinds of modules.

A Standard module is a stand alone collection of procedures (subs and
functions). How you group your procedures is up to you. I usually group
them by logical use. For example, I have one named modDateFunctions that
contains all the various routines I use for manipulating dates. Another is
an API and its associated code to manipulate the File Open/Save dialog.

A Class module is much like a standard module except that it defines a class
and is instansiated and used as a class.

A Form or Report Module belongs to the Form or Report object. It is
intended to hold the code specific to the form or report object. There is a
property of a form or report named Has Module. Its default is False;
however, as soon as you open the code builder for the form and create code
for the form/report, it changes to True. You open the code builder by either
selecting an event from the Properties dialog and selecting Code Builder or
you can click on the code icon on the menu bar and it will open the VBA
editor with the form's code in the active window.

Code in any module can reference code in any other module based on the Scope
of the procedure. The same is true of variables. How much you may know
about object scoping, I don't know, so I wont bore you with those details
unless you need more information.

Post back if you have more questions.

 

[gbostock]

I don't have a link for you, but I think I can explain it so you will get the
idea.
You are correct about Me. It is a reference to the form the code is in. It
can also be a report.

All VBA code is either a Sub or a Function. In reality, there is little
difference, Mainly a Function returns a value and a Sub does not. Either
will accept arguments.
All Subs and Functions are collected in Modules. There are basicly three
kinds of modules.

A Standard module is a stand alone collection of procedures (subs and
functions). How you group your procedures is up to you. I usually group
them by logical use. For example, I have one named modDateFunctions that
contains all the various routines I use for manipulating dates. Another is
an API and its associated code to manipulate the File Open/Save dialog.

A Class module is much like a standard module except that it defines a class
and is instansiated and used as a class.

A Form or Report Module belongs to the Form or Report object. It is
intended to hold the code specific to the form or report object. There is a
property of a form or report named Has Module. Its default is False;
however, as soon as you open the code builder for the form and create code
for the form/report, it changes to True. You open the code builder by either
selecting an event from the Properties dialog and selecting Code Builder or
you can click on the code icon on the menu bar and it will open the VBA
editor with the form's code in the active window.

Code in any module can reference code in any other module based on the Scope
of the procedure. The same is true of variables. How much you may know
about object scoping, I don't know, so I wont bore you with those details
unless you need more information.

Post back if you have more questions.
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

Thanks for your reply, sorry it took so long to get back to you. This
has been helpful but I've a long way to go. Currently I have a problem
where some of the form elements are not being populated as they once
were. So there are cases where a Me.element is null when it should
have been populated. The VBA code that deals with that element has not
been changed, so I'm having trouble seeing why it is no longer being
populated. This was on a hidden cell and when I made it visible it
showed how it was not being filled in. I know this is not enough for
you to go on. I'll just keep working it.

 

[Guest]

Okay.
First, it is not a cell. It is a control. Cells are in Excel.
Look at the properties dialog for the control and see whether it is a bound
control. You tell that by looking in the Data tab for the Control Source
property. If it has the name of a field in the form's recordset, then it is
a bound control and should populate with the value in the recordset and
should update the recordset when changed.

If there is something other than a field name, it may be referencing an
expression or another control. It is also possible that some code in the
form's module populates the control. You will just have to search for it.

 

[gbostock]

Okay.
First, it is not a cell. It is a control. Cells are in Excel.
Look at the properties dialog for the control and see whether it is a bound
control. You tell that by looking in the Data tab for the Control Source
property. If it has the name of a field in the form's recordset, then it is
a bound control and should populate with the value in the recordset and
should update the recordset when changed.

If there is something other than a field name, it may be referencing an
expression or another control. It is also possible that some code in the
form's module populates the control. You will just have to search for it.
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

It's a control that is bound to a column in a table. I changed the
table that it was using to a new, but very similar one. The column on
the old table was a primary key with identity. My new table didn't
have those attributes set so I fixed that in the new table, but that
didn't solve the problem.

Thanks...

 

[Guest]

Is that table the record source for the form?

 

[gbostock]

Is that table the record source for the form?
--
Dave Hargis, Microsoft Access MVP







- Show quoted text -

Yes

 

[Guest]

Given what you are saying, there should be no issue. If the control on the
form is visible, and the fore color and back color properties for the control
are Not the same and the control is bound to a field in the form's Record
Source, there is no reason it should not be visible.

What happens if you put some data in the control? Does it update the record
correctly? If you latter go back to that record, is the value still updated?

 

[gbostock]

Given what you are saying, there should be no issue. If the control on the
form is visible, and the fore color and back color properties for the control
are Not the same and the control is bound to a field in the form's Record
Source, there is no reason it should not be visible.

What happens if you put some data in the control? Does it update the record
correctly? If you latter go back to that record, is the value still updated?
--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

It won't allow input. More info: in the old form the table was an
access table, now we're linked to a SQL Server table. This contro is
normally hidden, but I changed that so I could see if it was
populating or not. In both the old and the new forms, the word
(autonumber) in parentheses appears there before anything happens. In
the old form the next value appears after anything happens in the
form. In the new nothing happens. In the new form later VBA code fails
because this value isn't there.

Thanks...

 

[Guest]

SQL Sever does not support Autonumber data types. For SQL Server, you need
to use an IDENTITY field.

 

[gbostock]

SQL Sever does not support Autonumber data types. For SQL Server, you need
to use an IDENTITY field.
--
Dave Hargis, Microsoft Access MVP







- Show quoted text -

Yes, the SQL Server table has the identity column that is an
autonumber in the Access table.

 

[Guest]

I still don't see the problem..

 

[gbostock]

I still don't see the problem..

I don't either. I'm still working it. If I figure it out I'll let you
know.

Thanks for the help anyway.