Numbering records

[Guest]

I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!

 

[Guest]

Try this Tara,


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If (Me.[FamilyID]) >= 1 Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub


Note the condition now requires the value to be greater than or equal to one
before the rest of the code adds 1. You should probably create the default
value of 'zero' in the table, that way when the record is created, if no
value is assigned using the form, then the table will at least insert a zero.

HTH

 

[Guest]

Thanks Paul, I think this has taken care of a couple of things, but I'm still
having an issue. Now, the record shows up as HV #0. I'm not sure if this
has anything to do with it, but it might, so in order to be absolutely clear,
let me explain a bit about the design of the database. I have one main form
that has 9 other subforms embedded in it. Upon opening the database, all the
subforms are hidden until you click a command button to make the form you
need visible. When you are done using that form, you simply hit another
command button to make the next form you need visible and the form you were
working on then becomes invisible. Could this process (the fact that the
SbfHV subform is already open when you first enter the database, just not
visible) be causing an issue? If so, how do I get around it?

Thanks!

Try this Tara,


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If (Me.[FamilyID]) >= 1 Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub


Note the condition now requires the value to be greater than or equal to one
before the rest of the code adds 1. You should probably create the default
value of 'zero' in the table, that way when the record is created, if no
value is assigned using the form, then the table will at least insert a zero.

HTH

I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!

 

[Guest]

Hi Tara,

The fact that you have these forms already open could be part of the
problem, I really don't know for sure. The best way to find out is to look at
each form and see what if any data is showing (I am assuming you don't want
anything showing until the user 'opens' the form).

Now I am no expert, but I do not open any forms until they are needed, that
way I know exactly what data is going into what form and by closing the form
after finishing with it, I am ensuring that (through code) the data entered
has been saved or discarded.

HTH

 

[Guest]

Paul, I'm pretty sure that's the problem. But because of the way the users
wanted the database to function, and because of my limited knowledge of VB,
the only way I could accomplish it was to use subforms. Would there be a way
to get around the issue, or an easy way to make this particular form a
stand-alone form, but still carry over the info I need from the main form?
Any help is appreciated!

Hi Tara,

The fact that you have these forms already open could be part of the
problem, I really don't know for sure. The best way to find out is to look at
each form and see what if any data is showing (I am assuming you don't want
anything showing until the user 'opens' the form).

Now I am no expert, but I do not open any forms until they are needed, that
way I know exactly what data is going into what form and by closing the form
after finishing with it, I am ensuring that (through code) the data entered
has been saved or discarded.

HTH

I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!

 

[Guest]

Tara, this is what I do,


stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo]"

DoCmd.OpenForm stDocName, , , stLinkCriteria


The 1st line sets the name of the form to open. This makes it easy to follow
and modify at a later time when I have fogotten what I was thinking.

the 2nd line determines what criteria I want the match. This is typically a
pick list, textbox or user input box. Make sure the formatting matches the
data type for the box you use.

The 3rd line is where I open the form and present the data to the user.

Of course there are many ways to use this example, for one, you can use
multiple criteria and use IF / ELSE options.


So, just to clarify, my main form asks the user to select a Station Number,
and when they click the 'Search' button, this returns the records in a new
form that match the station number. I did not include the error checking
lines (to make sure they actually picked a station number from the list prior
to clicking 'Search') here.

HTH

Paul, I'm pretty sure that's the problem. But because of the way the users
wanted the database to function, and because of my limited knowledge of VB,
the only way I could accomplish it was to use subforms. Would there be a way
to get around the issue, or an easy way to make this particular form a
stand-alone form, but still carry over the info I need from the main form?
Any help is appreciated!

Hi Tara,

The fact that you have these forms already open could be part of the
problem, I really don't know for sure. The best way to find out is to look at
each form and see what if any data is showing (I am assuming you don't want
anything showing until the user 'opens' the form).

Now I am no expert, but I do not open any forms until they are needed, that
way I know exactly what data is going into what form and by closing the form
after finishing with it, I am ensuring that (through code) the data entered
has been saved or discarded.

HTH

I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!

 

[Guest]

Paul, I hate to ask for anymore help, but I just can't seem to get this
right! I made the subform a stand-alone form and changed the code behind the
command button to match the example you gave me, but I'm not getting the
FamilyID to carry over (it always puts the record under family #1) and I'm
also still not getting the HV number to calculate and update. What in the
world am I doing wrong?????

Here's the code behind the comand button:

stDocName = "FrmHV1"
stLinkCriteria = "[FamilyID]"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Here's the code in the BeforeUpdate event of the form itself:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngHVNumber As Long

If (Me.[FamilyID]) >= 1 Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

BTW-Should [Else... Me.FamilyID = 1] be [Else...Me.HVNumber = 1] instead?
Not that it matters. I've tried it both ways and I can't get it to work.
Thanks for any additional help!!

Tara, this is what I do,


stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo]"

DoCmd.OpenForm stDocName, , , stLinkCriteria


The 1st line sets the name of the form to open. This makes it easy to follow
and modify at a later time when I have fogotten what I was thinking.

the 2nd line determines what criteria I want the match. This is typically a
pick list, textbox or user input box. Make sure the formatting matches the
data type for the box you use.

The 3rd line is where I open the form and present the data to the user.

Of course there are many ways to use this example, for one, you can use
multiple criteria and use IF / ELSE options.


So, just to clarify, my main form asks the user to select a Station Number,
and when they click the 'Search' button, this returns the records in a new
form that match the station number. I did not include the error checking
lines (to make sure they actually picked a station number from the list prior
to clicking 'Search') here.

HTH

Paul, I'm pretty sure that's the problem. But because of the way the users
wanted the database to function, and because of my limited knowledge of VB,
the only way I could accomplish it was to use subforms. Would there be a way
to get around the issue, or an easy way to make this particular form a
stand-alone form, but still carry over the info I need from the main form?
Any help is appreciated!

Hi Tara,

The fact that you have these forms already open could be part of the
problem, I really don't know for sure. The best way to find out is to look at
each form and see what if any data is showing (I am assuming you don't want
anything showing until the user 'opens' the form).

Now I am no expert, but I do not open any forms until they are needed, that
way I know exactly what data is going into what form and by closing the form
after finishing with it, I am ensuring that (through code) the data entered
has been saved or discarded.

HTH



:

I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!

 

[Guest]

Tara,

I may not have time today to work on this, but I will try and get an answer
to you as soon as I can.

If you send me your email address, I can contact you directly.

Paul, I hate to ask for anymore help, but I just can't seem to get this
right! I made the subform a stand-alone form and changed the code behind the
command button to match the example you gave me, but I'm not getting the
FamilyID to carry over (it always puts the record under family #1) and I'm
also still not getting the HV number to calculate and update. What in the
world am I doing wrong?????

Here's the code behind the comand button:

stDocName = "FrmHV1"
stLinkCriteria = "[FamilyID]"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Here's the code in the BeforeUpdate event of the form itself:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngHVNumber As Long

If (Me.[FamilyID]) >= 1 Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

BTW-Should [Else... Me.FamilyID = 1] be [Else...Me.HVNumber = 1] instead?
Not that it matters. I've tried it both ways and I can't get it to work.
Thanks for any additional help!!

Tara, this is what I do,


stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo]"

DoCmd.OpenForm stDocName, , , stLinkCriteria


The 1st line sets the name of the form to open. This makes it easy to follow
and modify at a later time when I have fogotten what I was thinking.

the 2nd line determines what criteria I want the match. This is typically a
pick list, textbox or user input box. Make sure the formatting matches the
data type for the box you use.

The 3rd line is where I open the form and present the data to the user.

Of course there are many ways to use this example, for one, you can use
multiple criteria and use IF / ELSE options.


So, just to clarify, my main form asks the user to select a Station Number,
and when they click the 'Search' button, this returns the records in a new
form that match the station number. I did not include the error checking
lines (to make sure they actually picked a station number from the list prior
to clicking 'Search') here.

HTH

Paul, I'm pretty sure that's the problem. But because of the way the users
wanted the database to function, and because of my limited knowledge of VB,
the only way I could accomplish it was to use subforms. Would there be a way
to get around the issue, or an easy way to make this particular form a
stand-alone form, but still carry over the info I need from the main form?
Any help is appreciated!

:

Hi Tara,

The fact that you have these forms already open could be part of the
problem, I really don't know for sure. The best way to find out is to look at
each form and see what if any data is showing (I am assuming you don't want
anything showing until the user 'opens' the form).

Now I am no expert, but I do not open any forms until they are needed, that
way I know exactly what data is going into what form and by closing the form
after finishing with it, I am ensuring that (through code) the data entered
has been saved or discarded.

HTH



:

I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!

 

[Guest]

Thanks for all the help Paul. Any time you get to it is fine...I really
appreciate it! You can email me at (e-mail address removed)

Tara,

I may not have time today to work on this, but I will try and get an answer
to you as soon as I can.

If you send me your email address, I can contact you directly.

Paul, I hate to ask for anymore help, but I just can't seem to get this
right! I made the subform a stand-alone form and changed the code behind the
command button to match the example you gave me, but I'm not getting the
FamilyID to carry over (it always puts the record under family #1) and I'm
also still not getting the HV number to calculate and update. What in the
world am I doing wrong?????

Here's the code behind the comand button:

stDocName = "FrmHV1"
stLinkCriteria = "[FamilyID]"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Here's the code in the BeforeUpdate event of the form itself:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngHVNumber As Long

If (Me.[FamilyID]) >= 1 Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

BTW-Should [Else... Me.FamilyID = 1] be [Else...Me.HVNumber = 1] instead?
Not that it matters. I've tried it both ways and I can't get it to work.
Thanks for any additional help!!

Tara, this is what I do,


stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo]"

DoCmd.OpenForm stDocName, , , stLinkCriteria


The 1st line sets the name of the form to open. This makes it easy to follow
and modify at a later time when I have fogotten what I was thinking.

the 2nd line determines what criteria I want the match. This is typically a
pick list, textbox or user input box. Make sure the formatting matches the
data type for the box you use.

The 3rd line is where I open the form and present the data to the user.

Of course there are many ways to use this example, for one, you can use
multiple criteria and use IF / ELSE options.


So, just to clarify, my main form asks the user to select a Station Number,
and when they click the 'Search' button, this returns the records in a new
form that match the station number. I did not include the error checking
lines (to make sure they actually picked a station number from the list prior
to clicking 'Search') here.

HTH


:

Paul, I'm pretty sure that's the problem. But because of the way the users
wanted the database to function, and because of my limited knowledge of VB,
the only way I could accomplish it was to use subforms. Would there be a way
to get around the issue, or an easy way to make this particular form a
stand-alone form, but still carry over the info I need from the main form?
Any help is appreciated!

:

Hi Tara,

The fact that you have these forms already open could be part of the
problem, I really don't know for sure. The best way to find out is to look at
each form and see what if any data is showing (I am assuming you don't want
anything showing until the user 'opens' the form).

Now I am no expert, but I do not open any forms until they are needed, that
way I know exactly what data is going into what form and by closing the form
after finishing with it, I am ensuring that (through code) the data entered
has been saved or discarded.

HTH



:

I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long

If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub

This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!