Help with VB Code!

[a24t42]

I am having problem adapting the multiuser counter code. I have a form
called Regulator. What I want is to look up the counter in a table
called Counter and set the control ID on the form to the counter. I
keep getting an error. Any help would be apprecaited. Here is the code
I have on a command button -

Private Sub cmdSave_Click()
Dim db As Database
Dim rs As Recordset
Dim NextNumber As Long
Dim ID As Long
Dim testmsg As Integer
Dim Code As String

Set db = OpenDatabase("D:\SouthEastern\STC_Stock\STC_Stock.mdb")
Set rs = db.OpenRecordset("Counter", dbOpenDynaset)

Me!ID = rs!NextNumber
Me!Code = "3PD"

rs.Edit
rs!NextNumber = rs!NextNumber + 1
rs.Update

testmsg = MsgBox("Do you want to add another Record?", 4, "Question?")
If testmsg = 6 Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.Close
End If

End Sub

Thanks in advance.

 

[storrboy]

Thanks in advance.

It would help to know what the error is you're getting and on what
line it occurs.

 

[a24t42]

I get error 2448 - You can't assign a value to this object. It stops
on the line -

Me!ID = rs!NextNumber

My knowledge of VB is minimal. Any help would be appreciated.

 

[storrboy]

I get error 2448 - You can't assign a value to this object. It stops
on the line -

Me!ID = rs!NextNumber

My knowledge of VB is minimal. Any help would be appreciated.

What are Me!ID and Me!Code? Are they controls on a form or are they
supposed to be the vaiables you Dim'd at the begining of the sub? If
they are controls, do they have a value property? Are you setting a
label to equal the field in the recordset? Labels and some other
controls don't have value properties.

I don't see ID and Code variables used in your code. You may also
encounter a naming conflict if they are the same as controls on your
form.

 

[a24t42]

As I said my knowledge of VB is minimal. I must not have something set
correctly. Here is what I have -

Table - Counter with one text box called NextNumber (long number,
primary key)

Form - Regulator
Text Box - ID (long number, primary key)
Text Box - Code (text,4)

What I am trying to do is when the record is saved by hitting a
command button is -
1. set Code="3PD"
2. set ID = NextNumber
3. Increment NextNumber by 1 and save it

I have tried to adapt the article in Microsoft Knowledge Base but I
guess I screwed something up. i am sorry but I do not know what you
mean "Value properties." Any help would be MOST appreciated.

Obviously Confused,
Judy

 

[storrboy]

As I said my knowledge of VB is minimal. I must not have something set
correctly. Here is what I have -

Table - Counter with one text box called NextNumber (long number,
primary key)

Form - Regulator
Text Box - ID (long number, primary key)
Text Box - Code (text,4)

What I am trying to do is when the record is saved by hitting a
command button is -
1. set Code="3PD"
2. set ID = NextNumber
3. Increment NextNumber by 1 and save it

I have tried to adapt the article in Microsoft Knowledge Base but I
guess I screwed something up. i am sorry but I do not know what you
mean "Value properties." Any help would be MOST appreciated.

Obviously Confused,
Judy

Start by putting an apostrophy in front of these two lines near the
top of your code so they look like this.
'Dim ID As Long
'Dim Code As String

This will turn them into comment lines - I don't see them used
anywhere so it should not hurt anything. See if this works. I think it
may be a conflict between the textboxes and the variables having the
same name. If this dosen't work, check to see if AllowEdits is set to
True(Yes) in the property sheet of the form.

 

[a24t42]

Thanks, that worked but I have one more quick question. The way it is
set up now, the text box is not set equal to the counter until I am
ready to save the record. I would like to be able to see the ID when I
am putting in the data (ie before saving). Everything I have tried
gives me the error 2448 again. How can I do this? Do I need to use
BeforeUpdate property? Thanks in advance.

 

[storrboy]

Thanks, that worked but I have one more quick question. The way it is
set up now, the text box is not set equal to the counter until I am
ready to save the record. I would like to be able to see the ID when I
am putting in the data (ie before saving). Everything I have tried
gives me the error 2448 again. How can I do this? Do I need to use
BeforeUpdate property? Thanks in advance.

Use the event that starts the creation of a new record. If this is a
command button or from a menu, place it there, otherwise, the likely
place would be the forms BeforeInsert event. This should occur after
the user enters the first character of new record.

 

[a24t42]

I have this form set to Data Entry set to Yes so I guess the
BeforeUpdate for the form is the best place.
----------------------------------
I tried something else and now get a different error. I set -

ID = DLookUp("[NextNumber]","Counter")

I put the following code behind the command button -

Dim db As Database
Dim rs As Recordset
Dim NextNumber As Long
Dim testmsg As Integer

Set db = OpenDatabase("D:\SouthEastern\STC_Stock\STC_Stock.mdb")
Set rs = db.OpenRecordset("Counter", dbOpenDynaset)

rs.Edit
rs!NextNumber = rs!NextNumber + 1
rs.Update

testmsg = MsgBox("Do you want to add another Record?", 4, "Question?")
If testmsg = 6 Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.Close
End If

------------------------------------------
The incrementing of the file is working fine. But if I choose yes, I
get the error 2105 " You can't go to specified record." If I choose
No, the Counter gets incremented but the file is not saved.

It seems the more I work on this, the more I confuse myself. What
would you suggest is the best way to -

1. set Code="3PD"
2. set ID = NextNumber
3. Increment NextNumber by 1 and save it

on a form set to Data entry yes.

I do appreciate your help.
Judy

Judy

 

[storrboy]

Why aren't you just using an AutoNumber?
This is a lot of work to do the same thing.

 

[a24t42]

Beacause I need to have a custom counter. This is a new database but
with the data that is being put into are numbered currently set at
3298. They want to start from here.

 

[storrboy]

Try this in a copy of database and see if saves you anymore time...

From the A97 help files.

--------------------------------------------------------------------------
Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting
value of an AutoNumber field that has its NewValues property set to
Increment to a number other than 1. For a table that contains records,
you can also use this procedure to change the next value assigned in
an AutoNumber field to a new number.

1)Create a temporary table with just one field, a Number field; set
its FieldSize property to Long Integer and give it the same name as
the AutoNumber field in the table whose value you want to change.

How?

2)In Datasheet view, enter a value in the Number field of the
temporary table that is 1 less than the starting value you want for
the AutoNumber field. For example, if you want the AutoNumber field to
start at 100, enter 99 in the Number field.
3)Create and run an append query to append the temporary table to the
table whose AutoNumber value you want to change.

How?

Note If your original table has a primary key, you must temporarily
remove the primary key before running the append query. Also, if your
original table contains fields that have the Required property set to
Yes, the Indexed property set to Yes (No Duplicates), or field and/or
record ValidationRule property settings that prevent Null entries in
fields, you must temporarily disable these settings.

4 Delete the temporary table.
5 Delete the record added by the append query.
6 If you had to disable property settings in step 3, return them to
their original settings.

When you enter a record in the remaining table, Microsoft Access uses
an AutoNumber field value 1 greater than the value you entered in the
temporary table.

Note If you want to compact the database after changing the starting
AutoNumber value, make sure to add at least one record to the table
first. If you don't, when you compact the database, the AutoNumber
value for the next record added will be reset to 1 more than the
highest previous value. For example, if there were no records in the
table when you reset the starting value, compacting would set the
AutoNumber value for the next record added to 1; if there were records
in the table when you reset the starting value and the highest
previous value was 50, compacting would set the AutoNumber value for
the next record added to 51.

 

[a24t42]

Thanks for the suggestion and help. I will look at it in detail
tomorrow. The other reason I was not using auto number is because
everything I have read on this forum about auto number doesn't suggest
you use it if you need a sequential numbering system, which I do. Auto
nunmber can have gaps in the numbers, which won't be good.

Once again thanks for all your help.

Judy

 

[storrboy]

Custom sequential numbers can as well if you delete records. It can
also occur when two records are added at the same time. If both new
records think that 14 is the next number, only one will get it (if no
duplicates are enforced). There is no fool proof method that I know of
and I don't see the point in re-creating what's already there. I use
them quite often. One of the main reasons I believe it's not
appropriate to use them, is when they need to mean something. They are
not intended to be meaningful IDs.

 

[a24t42]

And that is why I was going with custom. The counter is also serving
as invoice number on the inout form, which is a specific number.

Judy

 

[a24t42]

That is why I was going to custom. The counter also serves as an
unique invoice number.

 

[storrboy]

That is why I was going to custom. The counter also serves as an
unique invoice number.

Fair enough.
However I'm afraid I'm getting a bit lost in what you are doing.
I gather you have a table that stores a list of assigned ID's (invoice
#'s) and that you would like to know the next number before an invoice
is saved?
If so, I would approach it this way - keep in mind at this point I
don't know what button is doing what on what form to what table..

1) Make a function that returns the next available number, and only
does this. Don't attempt to change things on the form with it, just
return the next number. It could use the DMax function on the
NextNumber field, or create a recordset sorted by ID and look at the
last line +1 etc. Return the next number ie..
Function MyNextNumber() As Long
'get next number
'blah,blah
MyNextNumber=rs!NextNumber
End Function

2) In the BeforeInsert event, set the ID textbox on the form to be the
return value of the above function. When the first character of a new
record is typed in, the ID textbox will be filled with the new number.
ie..
Private Sub Form_BeforeInsert
Me!ID = MyNextNumber
End Sub

3) In the forms BeforeUpdate check your main table to ensure the ID
that was retreived is not already in use (new entry saved by someone
else before you). If it is, either warn the user that the ID is
already in use, or automatically change the ID by running the
MyNextNumber again and giving the ID textbox the new number. Cancel
the event if anything is wrong or the save needs to be prevented.

4) In the AfterUpdate event, add the newly saved ID to the NextNumber
table. You can do so with an Insert query...

Private Sub Form_AfterUpdate
DoCmd.RunSQL "INSERT INTO NextNumberTable (NextNumber) VALUES
(Me!ID);", False
End Sub

I'm not sure when the Code field needs to be assigned a value. If it's
always going to be "3PD" then just set the control's DefaultValue
property as "3PD".