Field help

[Iram]

Hello.

I need some help with some automation in Access 2003. I have a text field on
a form called "CalwinNumb". It's a case number field that sometimes we need
to enter based upon certain criteria such as with a an end reult of
"1B06N48", "c332965", etc...
If for some reason we don't have enough info to create the criteria then we
will need to create our own auto number starting at 5000 and have it
increment from there. The way I need it to work is, I would like to click a
button next to the "CalwinNumb" field that automates putting in the next
available auto number. How do you suggest I do this? Do you know of vba to do
this?

Thanks.
Your help is greatly appreciated.
I'll be out of town over the week and read your input on Tuesday 3/17.

Iram

 

[Albert D. Kallal]

Create a table with ONE field in it.

You can then using the following code:

Public Function nextinvoice() As Long

Dim myrecs As Recordset

Set myrecs = CurrentDb.OpenRecordset("tblNextInvoice")

nextinvoice = myrecs!nextinvoice

myrecs.Edit
myrecs!nextinvoice = myrecs!nextinvoice + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


So, to get a new invoice number, the code behind a button could be:


me.InvoiceNum = NextInvoice

In the above code example I assume that table has one field called
NextInvoice.

So, create the table, and add ONE record. You can then simply edit the value
of that field and enter 5000 as the starting point...

 

[Iram]

I think we are close but its not working, so let me tell you what i did...
Per your instruction I created a table called "tbl_NWNumbersUsed" with a
single number field called "NWNumber" (Field Size: Long Integer, default
value: 0) and added one record "5000".
Then I created a Module called "mdl_NextNWNumber", altered the code you sent
me and placed it in the module. The modified code looks like this...
Public Function NextNWNumber() As Long

Dim myrecs As Recordset

Set myrecs = CurrentDb.OpenRecordset("tblNextNWNumber")

NextNWNumber = myrecs!NextNWNumber

myrecs.Edit
myrecs!NextNWNumber = myrecs!NextNWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function

Then I created a button called "btn_NeedAnNWNumber" on the Data Entry called
"frmQuestionnaireAdd". On the "On Click" of this button I put the following
code under [Event Procedure]

Private Sub Btn_NeedAnNWNumber_Click()
Me.CalwinNumb = NextWNNumber
End Sub


"CalwinNumb" is the name of the field on the form that needs to be
autopopulated with the next incremental. When I click on the button, the
screen flickers and nothing is added to this field and there is no error.

Can you please help?
Thanks.
Iram

 

[Iram]

Correction, I put the wrong table name it should be like this
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
However after making this change its still not working...

Iram

 

[Albert D. Kallal]

Correction, I put the wrong table name it should be like this
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
However after making this change its still not working...

Does your code compile? (debug->compile?)

Also, for all code modules, you want to ensure that you use Option Explicit.

Option Compare Database
Option Explicit

Also, does any other code you have run correctly? (perahps you maco security
is set to prevent code from running?).

Also, after you type in that code, save, do a debug-compile, then you can in
the debug window simply type:

? NextNWNumber

Does the above work in the debug window?

 

[Iram]

The code to create "the next available NW number" compiles however I have
other code that does not compile. I am working with Dale Fye in the
newsgroups to fix the the other code that deals with verifying required
fields are filled in before closing a form.
I didn't have Option Explicit but now I have added it but now I get an error
when I click the button to create an NW for me. The error is "Compile Error:
Variable not defined".
Then "Public Function NextNWNumber() As Long" becomes yellow highlighted and
"NWNumber on the sixth line below becomes Grey highlighted in the below
code... Can you help me with this?

Option Compare Database
Option Explicit
Public Function NextNWNumber() As Long
Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function




Iram

 

[Albert D. Kallal]

try:

Option Compare Database
Option Explicit

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function

The above is a GREAT example why one wants to have "option explicit" always
specified.

Option explicit means that all variables MUST be declared correctly by you
or you code does not compile.

In your original you have:

NWNumber = myrecs!NWNumber

The above is wrong, I changed the above to:

NextNWNumber = myrecs!NWNumber

Also, in the above, we assume the column name in the table is NWNumber

So, if we did not use option explicit, then the above code would compile,
but would still be wrong. It would be VERY difficult to find the above error
without option explicit.

some people don't use option explicit because then you don't have to declare
any variables...you can type in whatever you want but then this tends to be
error prone.

 

[Iram]

When I click on my button on the form nothing happens. Let me give you a real
quick summary of what I got going again...
I have a data entry table called "tblQUESTIONNAIRE". Within this table I
have many fields but there is one field called "CalwinNumb" that I need to
either type a special case number or click a button on a form to give me the
next sequential number starting from 5000. I have another table called
"tbl_NWNumbersUsed" with one field in it called "NWNumber". I added one
record to this field "5000". On my data entry form I created a button and on
the On Click of it I have an expression "=NextNWNumber()". When I click on
the ... button next to it I get a vba screen with the following coming from a
module called "mdl_NextNWNumber"

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function



What am I doing wrong?
Please help.

Thanks.
Iram

 

[Albert D. Kallal]

I would place that next number code in a standard code module.

Save it, and compile it.

Now, in the debug window (ctrl-g), type in:

? nextNWNumber()

You should see a number get printed. What happens when you type in the above
in the debug window?