form question/loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im making a form in which automatically generates a company number once the
user inputs the name of a company.
I tried to use this code to generate a number for the company (code),but it
only runs correctly once.

Private Sub name_LostFocus()
Dim kount As Integer

For kount = 500 To 5000 Step 1
If name = j & j Then
code = 500
Else
code = kount + 1
End If
Next kount
End Sub

The form is divided into two parts (company table info & document table
info). On the document table info side of the form, i want to make a field at
the end of each document record thats supposed to be like a four digit
autonumber field starting at 0100. I also want to find a way to make the
autonumber field start back at 0100 when i enter a new company to the form.
Im not sure about how should I go about doing this.
 
Just a couple of notes. First, you probably don't want to do this on an event
associated with the company name. Otherwise, if you go to an existing company
to edit the name you will trigger the LostFocus event, thus generating a new
company ID for that company.

Now, your loop. It is working correctly, which is to find the next number
(501) if that number is already taken. What I think you really want to do is
find the highest number used and add one to that. You could do it on the
Name_AfterUpdate, as in my example below, but even that would generate a new
company ID if the name were edited.

Also, I would stay away from "Name" and "Code" as field/control names
because these can cause confusion with reserved words in Access. I would use
perhaps CompanyName and CompanyCode.

Private Sub CompanyName_AfterUpdate()
If DCount ("[CompanyCode]","[CompanyTableName]") = 0 then 'assigns 500 to
first company
CompanyCode = 500
Else
CompanyCode = DMax ("[CompanyCode]","[CompanyTableName]") + 1 'assigns
next number to company
End If
End Sub

Better yet (assuming you have a text box displaying the value of
CompanyCode), set the default value of CompanyCode to this:

= IIf (DCount ("[CompanyCode]","[CompanyTableName]") = 0,500, DMax
("[CompanyCode]","[CompanyTableName]") + 1)

This way, the next number comes up automatically. If it depends on an event
of the CompanyName field, the company would be assigned a new number if you
went back in to change the spelling of the company name (thus triggering the
LostFocus or AfterUpdate event).

Concerning your document numbers, you cannot reset an autonumber. Instead,
treat it much the same as the CompanyCode, but add a "where CompanyCode =
<CurrentCompanyCodeGoesHere>" when getting your DMax so that the sequence
starts with 100 or 100 for each company's documents. Your Document table will
need to have the DoucmentNumber and CompanyCode fields at a minimum, and the
Documents form will need to have a text or combo box that contains the
CompanyCode. The default value of the document number could be:

= IIf (DCount ("[DocumentCode]","[DocumentTableName]","[CompanyCode] = " &
[CompanyCode]) = 0,100, DMax
("[CompanyCode]","[CompanyTableName]"]","[CompanyCode] = " & [CompanyCode]) +
1)

This sets the DocumentNumber to 100 if this is the first document for this
company, and adds 1 sequentially as documents are added for the company; each
company gets its own sequence starting with 100.
 
The companycode numbering statement works fine, but im having a problem with
the code for the numbering for the doc_number attribute. I get a data type
mismatch error. I made the datatype of companycode as text.

Private Sub doc_number_Click()
If DCount("[companycode]", "documents", "[companycode] = " & [companycode])
= 0 Then
doc_number = 100
Else
doc_number = DMax("[doc_number]", "documents") + 1
End If
End Sub

Brian said:
Just a couple of notes. First, you probably don't want to do this on an event
associated with the company name. Otherwise, if you go to an existing company
to edit the name you will trigger the LostFocus event, thus generating a new
company ID for that company.

Now, your loop. It is working correctly, which is to find the next number
(501) if that number is already taken. What I think you really want to do is
find the highest number used and add one to that. You could do it on the
Name_AfterUpdate, as in my example below, but even that would generate a new
company ID if the name were edited.

Also, I would stay away from "Name" and "Code" as field/control names
because these can cause confusion with reserved words in Access. I would use
perhaps CompanyName and CompanyCode.

Private Sub CompanyName_AfterUpdate()
If DCount ("[CompanyCode]","[CompanyTableName]") = 0 then 'assigns 500 to
first company
CompanyCode = 500
Else
CompanyCode = DMax ("[CompanyCode]","[CompanyTableName]") + 1 'assigns
next number to company
End If
End Sub

Better yet (assuming you have a text box displaying the value of
CompanyCode), set the default value of CompanyCode to this:

= IIf (DCount ("[CompanyCode]","[CompanyTableName]") = 0,500, DMax
("[CompanyCode]","[CompanyTableName]") + 1)

This way, the next number comes up automatically. If it depends on an event
of the CompanyName field, the company would be assigned a new number if you
went back in to change the spelling of the company name (thus triggering the
LostFocus or AfterUpdate event).

Concerning your document numbers, you cannot reset an autonumber. Instead,
treat it much the same as the CompanyCode, but add a "where CompanyCode =
<CurrentCompanyCodeGoesHere>" when getting your DMax so that the sequence
starts with 100 or 100 for each company's documents. Your Document table will
need to have the DoucmentNumber and CompanyCode fields at a minimum, and the
Documents form will need to have a text or combo box that contains the
CompanyCode. The default value of the document number could be:

= IIf (DCount ("[DocumentCode]","[DocumentTableName]","[CompanyCode] = " &
[CompanyCode]) = 0,100, DMax
("[CompanyCode]","[CompanyTableName]"]","[CompanyCode] = " & [CompanyCode]) +
1)

This sets the DocumentNumber to 100 if this is the first document for this
company, and adds 1 sequentially as documents are added for the company; each
company gets its own sequence starting with 100.

tope12 said:
Im making a form in which automatically generates a company number once the
user inputs the name of a company.
I tried to use this code to generate a number for the company (code),but it
only runs correctly once.

Private Sub name_LostFocus()
Dim kount As Integer

For kount = 500 To 5000 Step 1
If name = j & j Then
code = 500
Else
code = kount + 1
End If
Next kount
End Sub

The form is divided into two parts (company table info & document table
info). On the document table info side of the form, i want to make a field at
the end of each document record thats supposed to be like a four digit
autonumber field starting at 0100. I also want to find a way to make the
autonumber field start back at 0100 when i enter a new company to the form.
Im not sure about how should I go about doing this.
Click to expand...
Click to expand...
 
I'm not sure how the Where clause in your DCount can work if CompanyCode is
text. CompanyCode should be a Long Integer in both tables. The Where clause
would have to be this if CompanyCode is text: "[CompanyCode] = '" &
[CompanyCode] & "'" (the single quotes indicate that the portion of the
clause in between them resolves to a string - it gets a little confusing).

Make sure the doc_number is Long Integer in both tables and that the
companycode is either text or numeric in both tables. Since you are adding
one to the CompanyCode, I would assume you could live with both being numeric.

Also don't forget to put the Where clause (, "[companycode] = " &
[companycode]) in the DMax as well as the DCount, or the DMax will return the
number one higher than the highest document number used by ANY company, not
the one you are currently using. Here is a little different version that may
resolve this - it assumes Long Integer for both:

Private Sub doc_number_Click()
If DCount("[companycode]", "[documents]", "[companycode] = " &
[companycode]) = 0 Then
doc_number = 100
Else
doc_number = DMax("[doc_number]", "[documents]","[companycode] = " &
[companycode]) + 1
End If
End Sub

tope12 said:
The companycode numbering statement works fine, but im having a problem with
the code for the numbering for the doc_number attribute. I get a data type
mismatch error. I made the datatype of companycode as text.

Private Sub doc_number_Click()
If DCount("[companycode]", "documents", "[companycode] = " & [companycode])
= 0 Then
doc_number = 100
Else
doc_number = DMax("[doc_number]", "documents") + 1
End If
End Sub

Brian said:
Just a couple of notes. First, you probably don't want to do this on an event
associated with the company name. Otherwise, if you go to an existing company
to edit the name you will trigger the LostFocus event, thus generating a new
company ID for that company.

Now, your loop. It is working correctly, which is to find the next number
(501) if that number is already taken. What I think you really want to do is
find the highest number used and add one to that. You could do it on the
Name_AfterUpdate, as in my example below, but even that would generate a new
company ID if the name were edited.

Also, I would stay away from "Name" and "Code" as field/control names
because these can cause confusion with reserved words in Access. I would use
perhaps CompanyName and CompanyCode.

Private Sub CompanyName_AfterUpdate()
If DCount ("[CompanyCode]","[CompanyTableName]") = 0 then 'assigns 500 to
first company
CompanyCode = 500
Else
CompanyCode = DMax ("[CompanyCode]","[CompanyTableName]") + 1 'assigns
next number to company
End If
End Sub

Better yet (assuming you have a text box displaying the value of
CompanyCode), set the default value of CompanyCode to this:

= IIf (DCount ("[CompanyCode]","[CompanyTableName]") = 0,500, DMax
("[CompanyCode]","[CompanyTableName]") + 1)

This way, the next number comes up automatically. If it depends on an event
of the CompanyName field, the company would be assigned a new number if you
went back in to change the spelling of the company name (thus triggering the
LostFocus or AfterUpdate event).

Concerning your document numbers, you cannot reset an autonumber. Instead,
treat it much the same as the CompanyCode, but add a "where CompanyCode =
<CurrentCompanyCodeGoesHere>" when getting your DMax so that the sequence
starts with 100 or 100 for each company's documents. Your Document table will
need to have the DoucmentNumber and CompanyCode fields at a minimum, and the
Documents form will need to have a text or combo box that contains the
CompanyCode. The default value of the document number could be:

= IIf (DCount ("[DocumentCode]","[DocumentTableName]","[CompanyCode] = " &
[CompanyCode]) = 0,100, DMax
("[CompanyCode]","[CompanyTableName]"]","[CompanyCode] = " & [CompanyCode]) +
1)

This sets the DocumentNumber to 100 if this is the first document for this
company, and adds 1 sequentially as documents are added for the company; each
company gets its own sequence starting with 100.

tope12 said:
Im making a form in which automatically generates a company number once the
user inputs the name of a company.
I tried to use this code to generate a number for the company (code),but it
only runs correctly once.

Private Sub name_LostFocus()
Dim kount As Integer

For kount = 500 To 5000 Step 1
If name = j & j Then
code = 500
Else
code = kount + 1
End If
Next kount
End Sub

The form is divided into two parts (company table info & document table
info). On the document table info side of the form, i want to make a field at
the end of each document record thats supposed to be like a four digit
autonumber field starting at 0100. I also want to find a way to make the
autonumber field start back at 0100 when i enter a new company to the form.
Im not sure about how should I go about doing this.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top