A macro & module to send field data to a new record

[Donzie]

Hi -

I could sure use some help getting going in Modules -- Access 97. Once I get started, I think I can get the concept -- its that first step that is killing me.

So far, I have a successful MACRO that opens my "Products" database in a "paidout" form - with the cursor on the correct field. The macro seeks the last number in the "Itemnumber" field (not the last record in the database, but the last one below #1000, which is what I want.)

At that point is where I want to insert code that will grab that number and hold it in a variable that I want to call "myoldno" Then, returning to the Macro, I can already add a new record, with the cursor in the correct "itemnumber" field.
At that point I THINK I want to run a second function to insert the variable "myoldno" +1 into that Itemnumber field - ending the macro so I can fill out the rest of the form.
---
I THINK that my module has the right idea, but I feel like, even though the database is already open, the form is open, and the focus is on the right field, the module needs to be told all of this info again. I DIM'd my variable as PUBLIC so I wouldn't lose it between the two functions, but the module seems to have trouble figuring out WHAT I want to store as the variable. For the time being, I don't feel ready to do the whole routine as a module, unless I simply CAN'T mix the module into my macro.
---
My needs are totally BASIC (get it?) -- I have seen snippets with "recordsets", but don't know if I need one if I am only using the current record at any given time. Thanks in advance for any suggested code.

 

[Melagan]

Good day Donzie, welcome to the world of Visual Basic! It's at first an intimidating world, but will soon become your best friend, I'm sure.

For this application, I think the only thing you'll need a module for is to declare your global variable. The rest can and should be handled in code behind your form.

To declare your global variable, create your module and in the declarations section (very top), type in:

Public [COLOR=green]g[/color][COLOR=blue]str[/color]myoldno as [COLOR=blue] String[/color]

I suggest the g because it is a -g-lobal variable, and the str because it is a variable of String type. This is a standard naming convention. Although since your Itemnumber field is probably numeric, you might consider defining your variable as Double instead of String, in which case:

Public gdblmyoldno as Double

Now behind your form, you'll have to decide on an event that will trigger this code to populate your variable. Here is some example code to do so:

gdblmyoldno = Me.Itemnumber.Value

(where Itemnumber is the name of the CONTROL on your form whose control source is your Itemnumber field)

Additionally, you can set the focus of your form via code on the forms open event, like so:

Private Sub Form_Open(Cancel as Integer)

me.YourControlName.Setfocus
End Sub

Hopefully that'll get you started =)

~Melagan
______
"It's never too late to become what you might have been.