Visual Basic... Where do I start????

[Jon22]

I've been creating various databases for my little company for years using
the built in functions and avoiding the use of any Visual Basic. I'd like to
try to start using VB and have kind of got my head around a small part of it
(ie. event procedures that run a sub on a particular event such as on click)
but I have searched and searched the net and all the MS Access help files and
I can not for the life of me work out a simple thing like .. How do I set the
control source of a text box using VB??? What do I open to do it?

I've found plenty of info on what code to write such as:

Me!Expected.ControlSource = "=Date() + 7"

But where do I type this?????

 

[Tom Wickerath]

Hi Jon,

In the example you gave, you don't even need to use VBA code. Just set the
Control Source property for a text box to this expression:

=Date()+7


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jeanette Cunningham]

Hi Jon22,
You can code the text box to show the date you want like this-->
Me!Expected = DateAdd("d", 7, Date())

However the text box's control source is a different thing.
The control source tells the textbox which table to update with the value of
Expected for the field called Expected.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jon22]

Yeah I know - bad example Tom, I was actually wanting to have, let's call it
"textboxa" return the FileDateTime() property of a value in a listbox control
on the same form. I did a bit of a work-around by adding it to the list box's
After Update event procedure but I still have no idea how I would make the
control source of textboxa this value without having to have an event take
place.

 

[Tom Wickerath]

Ok, let's say that you have a list box on a form, named "lboFiles", with the
following properties:

RowSource:
SELECT pkFileID, FileName, FileLastModified FROM tblJPGFiles ORDER BY
FileName;

Column Count: 3
Column Widths: 0";1.75";1.25"
Multi Select: None (found on the Other tab of the Properties dialog)

You could use the following Control Source for a text box on the same form:

=[lboFiles].[column](2)

Note that the columns are "zero-based". In other words, the first field is
column(0), the second field is column(1), and the third field is column(2).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jeanette Cunningham]

You do need to have an event take place if you want to add 7 days to the
date selected in the listbox.

You need code in the after update event of the listbox.

If Not IsNull(Me.NameOfListbox) Then
Me!Expected = DateAdd("d", 7, Me.NameOfListbox.Column(1))
End If

where you choose the correct column of the listbox for the date you want.
Listbox column numbering starts at 0 for the first column.

The code in the after update of the listbox will change the value in textbox
called Expected every time a different file is selected in the listbox.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jon22]

But where do I write the line "=[lboFiles].[column](2)"?

Ok, let's say that you have a list box on a form, named "lboFiles", with the
following properties:

RowSource:
SELECT pkFileID, FileName, FileLastModified FROM tblJPGFiles ORDER BY
FileName;

Column Count: 3
Column Widths: 0";1.75";1.25"
Multi Select: None (found on the Other tab of the Properties dialog)

You could use the following Control Source for a text box on the same form:

=[lboFiles].[column](2)

Note that the columns are "zero-based". In other words, the first field is
column(0), the second field is column(1), and the third field is column(2).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Yeah I know - bad example Tom, I was actually wanting to have, let's call it
"textboxa" return the FileDateTime() property of a value in a listbox control
on the same form. I did a bit of a work-around by adding it to the list box's
After Update event procedure but I still have no idea how I would make the
control source of textboxa this value without having to have an event take
place.

 

[Tom Wickerath]

That would be entered as the Control Source property for the text box (no VBA
code required for this to work).

I need to call it a night now, so I'll have to wait until tomorrow to post
any further followups.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jon22]

Nup, still lost. Forget my DateAdd example. You know how you can set the
control source of a text box to a function rather than have it linked to a
field in the Form's underlying table? I'm trying to find out how you go about
doing the parrallel of such functions using VB. I'm well versed on using the
build or just typing in Domainaggs and IIf formulas etc into the Control
Source property field but can you open a control object in some kind of VB
window and type the source as VB?

 

[Wayne-I-M]

Hi Jon

I think you question referes more to do with understanding "how to" rather
than a specific question - may be wrong.

But (if as you say you have createing this DB for a while) you will not be
using a vista so can I suggest this book would be a good place to start.

http://www.amazon.com/exec/obidos/t...onsulinc/102-0569829-0593751?v=glance&s=books

It gives a clear understanding of how access works and you can take it at
your own pace.

Also, just a tip. Don't practice on your company DB but make a small new DB
and practice on that.

Good luck

 

[Jeanette Cunningham]

Why would you need to do that?
You can set a property in code.

However the line of code needs to be part of a sub or function.
There needs to be an event somewhere to make the line of code run.

Me.NameOfTextBox.ControlSource = NameOfFunction

You can set properties for several textboxes on several forms using code
stored in a general module and run that code to change those properties on
those text boxes from the code window. Is this what you are asking?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jon Lewis]

Jon

VBA code is written/contained in modules. You can create modules as you can
create forms or any other Access objects. When you create such a module
(known as a standard module) it is independent of any other object so you
would use it to store 'global' code to be used by perhaps several objects
(e.g. a function that is used by several forms).

Additionally, each Form can have its own Module (a class module) that
'belongs' only to the specific form (or instance of the form). Easiest way
to access/create the form's class module is to right click on any section or
object in the form and choose Build Event, select Code Builder and click OK.
You are now in the Form's class module. There are two drop down boxes. The
left one lists the Form, Sections and Controls and the right one lists the
built in events for the selected object. You put your code in the
appropriate event of the appropriate object.

Hope this helps you get the hang of things.

 

[Mark Andrews]

First it's VBA. VB.NET is different. Most people when you say "Visual
Basic" would think that's VB.NET or older VB6. Visual Basic for
Applications is slightly different.

I would:
- get a book
- check out some of the links
http://www.rptsoftware.com/help/microsoft_access_links/
Most code is put into events behind forms. So learning some of the common
events
such as Form_Load, button On Click etc.... is good.
- Look at other examples and try and understand what the code is doing
- Then just learn as you go
- When you can't figure out something check the book, look at the help
files, search the web and it all else fails post to this newsgroup

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[Fred]

You guys that wrote posts with a few of the basic "getting started" concepts
are smarter that all of the people writing the books. As far as I can see,
nobody has figured out the value & need for writing a "getting started on
Access Programming" book or what needs to be in it.

Sincerely,


Fred

 

[John Spencer]

Lets say you have a function in a VBA module

Public Function fPlusOneWeek()
fPlusOneWeek = DateAdd("D",7,Date())
End Function

That function will always return a date one week in the future.

Now you can set the control source to

Control Source: =fPlusOneWeek()

The control will always show the date plus seven days.

You could have that function as a Private Function in the Class module for a
form or report and then it would only be available on the form or report.

IF you wanted to set the control source the way you have you might use the
current event of the form to actually set the control source

Private Sub Form_Current()
Me!Expected.ControlSource = "=Date() + 7"
End Sub

Or
Private Sub Form_Current()
Me!Expected.ControlSource = "=fPlusOneWeek()"
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Mark Andrews]

Yes the guys that answer posts are pretty smart in general, I agree.

I haven't looked at an Access book in a while but last I remember there are
lots of them and they
mostly cover the "getting started" stuff. What is difficult is accumulating
the code needed to handle
lots of tricky situations. The web and this newsgroup are the places to
find that code, but not always easy to track down.

Generic relational database design is also a good way to start (such as a
course taught in college).

My two cents,
Mark
RPT Software
http://www.rptsoftware.com

 

[Jon22]

Thank you all for your help.

Lets say you have a function in a VBA module

Public Function fPlusOneWeek()
fPlusOneWeek = DateAdd("D",7,Date())
End Function

That function will always return a date one week in the future.

Now you can set the control source to

Control Source: =fPlusOneWeek()

The control will always show the date plus seven days.

You could have that function as a Private Function in the Class module for a
form or report and then it would only be available on the form or report.

IF you wanted to set the control source the way you have you might use the
current event of the form to actually set the control source

Private Sub Form_Current()
Me!Expected.ControlSource = "=Date() + 7"
End Sub

Or
Private Sub Form_Current()
Me!Expected.ControlSource = "=fPlusOneWeek()"
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County