help with code

[bbypookins]

Can someone please help me with this? I've been getting help from Klatuu but
I haven't gotten a response to my last few posts...I think he's given up on
me, which is understandable. For my original post, please see "form code to
create field." Here's my latest question.

I've got the current event working! Progress! But, I still can't get the
combo box to work.
Below is the code I have. I'm getting a "Compile error: Method or data
member not found" and it is highlighting ".Division =" of the first
Me.Division line.

Private Sub cboDivisionSelect_AfterUpdate()
Me.Division = Me.cboDivisionSelect
Me.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& Me.cboDivisionSelect & """"), 0) + 1
End Sub

 

[Klatuu]

Sorry bbypookins. Maybe I didn't click Notify me of replies.
I haven't given up, I just haven't received any notifications of your posts.

Okay, Me.Divison would be a text box control bound to the Division field in
your form's rowsource.

To bring me up to speed, did you decide to use two fields or are you still
wanting to use just one?

I can help better if you tell me that and the names of the controls on your
form that are involved and the field in your recordset they are bound to.

 

[bbypookins]

Yeah, you're out there! Thank you!

My Division field is actually a combo box bound to the query qryDivision
which is based on the table tblDivision.

I started a brand new form because it seemed there was something lurking in
the other one that was throwing me off. So, we can create whatever controls
you think will work for me. In my table I now have these fields: Division;
SeqNumber. In my form I have two controls with the same names. SeqNumber is
the combo box that has the code associated with it.

I was working on 2 fields with the understanding that we could later figure
out how to search by the combined fields.

Sorry bbypookins. Maybe I didn't click Notify me of replies.
I haven't given up, I just haven't received any notifications of your posts.

Okay, Me.Divison would be a text box control bound to the Division field in
your form's rowsource.

To bring me up to speed, did you decide to use two fields or are you still
wanting to use just one?

I can help better if you tell me that and the names of the controls on your
form that are involved and the field in your recordset they are bound to.
--
Dave Hargis, Microsoft Access MVP

Can someone please help me with this? I've been getting help from Klatuu but
I haven't gotten a response to my last few posts...I think he's given up on
me, which is understandable. For my original post, please see "form code to
create field." Here's my latest question.

I've got the current event working! Progress! But, I still can't get the
combo box to work.
Below is the code I have. I'm getting a "Compile error: Method or data
member not found" and it is highlighting ".Division =" of the first
Me.Division line.

Private Sub cboDivisionSelect_AfterUpdate()
Me.Division = Me.cboDivisionSelect
Me.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& Me.cboDivisionSelect & """"), 0) + 1
End Sub

 

[Klatuu]

Okay, your Division combo is fine.
I would not use a bound combo for SeqNumber. That is dangerous. If have an
existing record and make a selection in the combo expecting it to navigate to
a different record, what will really happen is you will be changing the value
of the existing record, not moving to a new record. You should use a text
box for SeqNumber as the bound control. If you want to search by SeqNumber,
you can use your existing combo, but make it unbound. Here is how you use a
combo to search for a record:

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Now, there may also be an issue with your Division combo box. If a user
changes the value for an existing record, you may end up with two records
with the same number for the division the user changed to. For example.
Lets say you are on Division XXX (sorry don't remember the real names) and
SeqNumber 123. The user changes the value to YYY and there happens to
already be a SeqNumber 123 for YYY. Now you have two. So, here is my
suggestion.

Make the Division combo enabled only for new records. When you create a new
record and the user selects a division, create the SeqNumber, set its Enabled
property to No and its Locked property to yes. First we need a bit of code
in the form's Current event:

Private Sub Form_Current()

With Me
If .NewRecord Then
.Division.Enabled = True
.Division.Locked = False
Else
.Division.Enabled = False
.Division.Locked = True
End If

Now in the After Update event of the Division combo, we assign the next
number for the division:

Private Sub cboDivisionSelect_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
""" & .Division """"), 0) + 1
End Sub


Just a note, I will have to check back from time to time. I did not get a
notification this time, either. I just happened to be scanning posts and saw
it again.
--
Dave Hargis, Microsoft Access MVP

Yeah, you're out there! Thank you!

My Division field is actually a combo box bound to the query qryDivision
which is based on the table tblDivision.

I started a brand new form because it seemed there was something lurking in
the other one that was throwing me off. So, we can create whatever controls
you think will work for me. In my table I now have these fields: Division;
SeqNumber. In my form I have two controls with the same names. SeqNumber is
the combo box that has the code associated with it.

I was working on 2 fields with the understanding that we could later figure
out how to search by the combined fields.

Sorry bbypookins. Maybe I didn't click Notify me of replies.
I haven't given up, I just haven't received any notifications of your posts.

Okay, Me.Divison would be a text box control bound to the Division field in
your form's rowsource.

To bring me up to speed, did you decide to use two fields or are you still
wanting to use just one?

I can help better if you tell me that and the names of the controls on your
form that are involved and the field in your recordset they are bound to.
--
Dave Hargis, Microsoft Access MVP

Can someone please help me with this? I've been getting help from Klatuu but
I haven't gotten a response to my last few posts...I think he's given up on
me, which is understandable. For my original post, please see "form code to
create field." Here's my latest question.

I've got the current event working! Progress! But, I still can't get the
combo box to work.
Below is the code I have. I'm getting a "Compile error: Method or data
member not found" and it is highlighting ".Division =" of the first
Me.Division line.

Private Sub cboDivisionSelect_AfterUpdate()
Me.Division = Me.cboDivisionSelect
Me.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& Me.cboDivisionSelect & """"), 0) + 1
End Sub

 

[bbypookins]

:

You should use a text

box for SeqNumber as the bound control. If you want to search by SeqNumber,
you can use your existing combo, but make it unbound. Here is how you use a
combo to search for a record:

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

I don't need to use this code if I canged SeqNumber to a text box, correct?

Private Sub Form_Current()

With Me
If .NewRecord Then
.Division.Enabled = True
.Division.Locked = False
Else
.Division.Enabled = False
.Division.Locked = True
End If

Now in the After Update event of the Division combo, we assign the next
number for the division:

Private Sub cboDivisionSelect_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
""" & .Division """"), 0) + 1
End Sub

Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with the
four quotes after &.Division highlighted.

 

[Douglas J. Steele]

Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.

You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[bbypookins]

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

..SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.

You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[Klatuu]

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.

You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[bbypookins]

Disregard my last question, I got it to work. Now, we've got these controls
working, but I have a request. Is there a way I can make the number always
three digits starting with 001?

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.


You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[Klatuu]

Numeric values are not stored with leading zeros, but you can format them
however you want for human consumption.
Try using @@@ in the format property of the control you show the field in.
The number will always start with 1 when there are no records for the
division.
--
Dave Hargis, Microsoft Access MVP

Disregard my last question, I got it to work. Now, we've got these controls
working, but I have a request. Is there a way I can make the number always
three digits starting with 001?

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

:


Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.


You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[bbypookins]

That didn't work. But I put 000 in there and that seems to.

Now, back to the searchingaspect of the form. You gave me the following info
before:

You should use a text
box for SeqNumber as the bound control. If you want to search by SeqNumber,
you can use your existing combo, but make it unbound. Here is how you use a
combo to search for a record:

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I put that code in, but I don't get how you're supposed to search for a
record. A user comes in and say they want to find the record FIN203, how do
they use the Find tool to do that?

Numeric values are not stored with leading zeros, but you can format them
however you want for human consumption.
Try using @@@ in the format property of the control you show the field in.
The number will always start with 1 when there are no records for the
division.
--
Dave Hargis, Microsoft Access MVP

Disregard my last question, I got it to work. Now, we've got these controls
working, but I have a request. Is there a way I can make the number always
three digits starting with 001?

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP


:

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

:


Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.


You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[Klatuu]

First, lets use a differnt name. I believe SeqNumber is the text box that is
bound to the SeqNumber field in your recordset.
I prefer to use prefixes on my names, so I know immediately what they are,
for example, all text box controls start with txt, so the text box would be
named txtSeqNumber. And a combo box would start with cbo.
Now, you your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
--
Dave Hargis, Microsoft Access MVP

That didn't work. But I put 000 in there and that seems to.

Now, back to the searchingaspect of the form. You gave me the following info
before:

You should use a text
box for SeqNumber as the bound control. If you want to search by SeqNumber,
you can use your existing combo, but make it unbound. Here is how you use a
combo to search for a record:

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I put that code in, but I don't get how you're supposed to search for a
record. A user comes in and say they want to find the record FIN203, how do
they use the Find tool to do that?

Numeric values are not stored with leading zeros, but you can format them
however you want for human consumption.
Try using @@@ in the format property of the control you show the field in.
The number will always start with 1 when there are no records for the
division.
--
Dave Hargis, Microsoft Access MVP

Disregard my last question, I got it to work. Now, we've got these controls
working, but I have a request. Is there a way I can make the number always
three digits starting with 001?

:

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP


:

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

:


Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.


You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[bbypookins]

Okay. I created an unbound combo box called cboFindRecord and entered your
code in the AFter Update event. Now how does the user utilize this?

First, lets use a differnt name. I believe SeqNumber is the text box that is
bound to the SeqNumber field in your recordset.
I prefer to use prefixes on my names, so I know immediately what they are,
for example, all text box controls start with txt, so the text box would be
named txtSeqNumber. And a combo box would start with cbo.
Now, you your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
--
Dave Hargis, Microsoft Access MVP

That didn't work. But I put 000 in there and that seems to.

Now, back to the searchingaspect of the form. You gave me the following info
before:

You should use a text
box for SeqNumber as the bound control. If you want to search by SeqNumber,
you can use your existing combo, but make it unbound. Here is how you use a
combo to search for a record:

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I put that code in, but I don't get how you're supposed to search for a
record. A user comes in and say they want to find the record FIN203, how do
they use the Find tool to do that?

Numeric values are not stored with leading zeros, but you can format them
however you want for human consumption.
Try using @@@ in the format property of the control you show the field in.
The number will always start with 1 when there are no records for the
division.
--
Dave Hargis, Microsoft Access MVP


:

Disregard my last question, I got it to work. Now, we've got these controls
working, but I have a request. Is there a way I can make the number always
three digits starting with 001?

:

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP


:

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

:


Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.


You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[Klatuu]

what is the row source of the combo?
The user would select from the combo's list
--
Dave Hargis, Microsoft Access MVP

Okay. I created an unbound combo box called cboFindRecord and entered your
code in the AFter Update event. Now how does the user utilize this?

First, lets use a differnt name. I believe SeqNumber is the text box that is
bound to the SeqNumber field in your recordset.
I prefer to use prefixes on my names, so I know immediately what they are,
for example, all text box controls start with txt, so the text box would be
named txtSeqNumber. And a combo box would start with cbo.
Now, you your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
--
Dave Hargis, Microsoft Access MVP

That didn't work. But I put 000 in there and that seems to.

Now, back to the searchingaspect of the form. You gave me the following info
before:

You should use a text
box for SeqNumber as the bound control. If you want to search by SeqNumber,
you can use your existing combo, but make it unbound. Here is how you use a
combo to search for a record:

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I put that code in, but I don't get how you're supposed to search for a
record. A user comes in and say they want to find the record FIN203, how do
they use the Find tool to do that?

:

Numeric values are not stored with leading zeros, but you can format them
however you want for human consumption.
Try using @@@ in the format property of the control you show the field in.
The number will always start with 1 when there are no records for the
division.
--
Dave Hargis, Microsoft Access MVP


:

Disregard my last question, I got it to work. Now, we've got these controls
working, but I have a request. Is there a way I can make the number always
three digits starting with 001?

:

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP


:

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

:


Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.


You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub

 

[bbypookins]

What is it supposed to be? I'm confused.

what is the row source of the combo?
The user would select from the combo's list
--
Dave Hargis, Microsoft Access MVP

Okay. I created an unbound combo box called cboFindRecord and entered your
code in the AFter Update event. Now how does the user utilize this?

First, lets use a differnt name. I believe SeqNumber is the text box that is
bound to the SeqNumber field in your recordset.
I prefer to use prefixes on my names, so I know immediately what they are,
for example, all text box controls start with txt, so the text box would be
named txtSeqNumber. And a combo box would start with cbo.
Now, you your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
--
Dave Hargis, Microsoft Access MVP


:

That didn't work. But I put 000 in there and that seems to.

Now, back to the searchingaspect of the form. You gave me the following info
before:

You should use a text
box for SeqNumber as the bound control. If you want to search by SeqNumber,
you can use your existing combo, but make it unbound. Here is how you use a
combo to search for a record:

Private Sub SeqNumber_AfterUpdate()
.Division.Enabled = True
.Division.Locked = False

With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I put that code in, but I don't get how you're supposed to search for a
record. A user comes in and say they want to find the record FIN203, how do
they use the Find tool to do that?

:

Numeric values are not stored with leading zeros, but you can format them
however you want for human consumption.
Try using @@@ in the format property of the control you show the field in.
The number will always start with 1 when there are no records for the
division.
--
Dave Hargis, Microsoft Access MVP


:

Disregard my last question, I got it to work. Now, we've got these controls
working, but I have a request. Is there a way I can make the number always
three digits starting with 001?

:

It is trying to find a control on your form name SeqNumber, but can't.
What is then name of the control?
Also, be sure the field names and table names are correct in the DLookup.
--
Dave Hargis, Microsoft Access MVP


:

Okay, that fixed that problem. Now when I try to select a division from the
list in the form, I get this error:

Compile error: Method or data member not found.

.SeqNumber. is highlighted.

I don't understand this because I have the form text field called SeqNumber
which is bound to the table field of the same name.

:


Here's exactly what I have:

Private Sub Division_AfterUpdate()
With Me
.SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] =
"""
& .Division """"), 0) + 1
End With
End Sub

I'm getting an error "Compile Error: Expected: list separator or )" with
the
four quotes after &.Division highlighted.


You're missing an ampersand after .Division (blame it on Dave: he forgot it
in his response! <g>)

Private Sub Division_AfterUpdate()
With Me
!SeqNumber = Nz(DLookup("[SeqNumber]", "tblRPALog", "[Division] = """
& .Division & """"), 0) + 1
End With
End Sub