using msg box to advance to new record

[The Old Guy]

wow, i'm back for more advice. the problem I have is that after completing a
search for a specific record and not finding it, how do I get the code to
advance to a new record instead of staying on the last record in the record
set. I'm using the following code to look for a record:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductNumber] = " & Str(Nz(Me![Text5]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What I wish to do is give the user the option of re-entering the product
number and trying again or deciding to enter the product as a new product.
Thanks for any help that can be given.

John (aka 'the old guy')

 

[Allen Browne]

Can we assume that Text5 is *unbound*?

Your code needs to:
a) See if the user entered anthing: otherwise the FindFirst string would be
mal-formed.

b) Explicitly save any edits (as you can't move without saving.)

c) Use a specific kind of recordset (rather than merely Object.)

d) Test NoMatch rather than EOF to see if there was a match.

e) Respond in 2 ways (find, or go new.)

Something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


You might also want to consider the cases where a record exists but might
not be found, e.g. if the form was filtered, or in DataEntry mode.

 

[The Old Guy]

Allen, thanks for the reply. yes, text 5 is unbound. do you think the
coding starting at 'response =' would work where the new record command is?
that has been the real problem area. trying to get it to work based on what
the user would select.




Dim MsgA, StyleA, TitleA, Response
Dim TempNbr As Single


MsgA = "Item Not Found! Click 'Yes' to add" _
& Chr$(13) & "this item. Click 'NO' to " _
& Chr$(13) & "re-enter the item number."

StyleA = vbYesNo + vbInformation + vbDefaultButton2
TitleA = "Item Data Error"

Response = MsgBox(MsgA, StyleA, TitleA)
If Response = 7 Then
Me.Text5 = ""
DoCmd.RunCommand acCmdRecordsGoToNew
Me.Text5.SetFocus
Exit Sub
Else
TempNbr = Me.Text5
DoCmd.RunCommand acCmdRecordsGoToNew
Me.ProductNumber = TempNbr
Me.ProductName.SetFocus
Exit Sub
End If

End If

Can we assume that Text5 is *unbound*?

Your code needs to:
a) See if the user entered anthing: otherwise the FindFirst string would be
mal-formed.

b) Explicitly save any edits (as you can't move without saving.)

c) Use a specific kind of recordset (rather than merely Object.)

d) Test NoMatch rather than EOF to see if there was a match.

e) Respond in 2 ways (find, or go new.)

Something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


You might also want to consider the cases where a record exists but might
not be found, e.g. if the form was filtered, or in DataEntry mode.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

wow, i'm back for more advice. the problem I have is that after
completing a
search for a specific record and not finding it, how do I get the code to
advance to a new record instead of staying on the last record in the
record
set. I'm using the following code to look for a record:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductNumber] = " & Str(Nz(Me![Text5]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What I wish to do is give the user the option of re-entering the product
number and trying again or deciding to enter the product as a new product.
Thanks for any help that can be given.

John (aka 'the old guy')

 

[Allen Browne]

The SetFocus won't work if this is the AfterUpdate event of Text5.

Set to Null rather than a zero-length string.

I guess you ignored all the other suggestions I gave, so you are not
interested in the difference they could make.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the reply. yes, text 5 is unbound. do you think the
coding starting at 'response =' would work where the new record command
is?
that has been the real problem area. trying to get it to work based on
what
the user would select.




Dim MsgA, StyleA, TitleA, Response
Dim TempNbr As Single


MsgA = "Item Not Found! Click 'Yes' to add" _
& Chr$(13) & "this item. Click 'NO' to " _
& Chr$(13) & "re-enter the item number."

StyleA = vbYesNo + vbInformation + vbDefaultButton2
TitleA = "Item Data Error"

Response = MsgBox(MsgA, StyleA, TitleA)
If Response = 7 Then
Me.Text5 = ""
DoCmd.RunCommand acCmdRecordsGoToNew
Me.Text5.SetFocus
Exit Sub
Else
TempNbr = Me.Text5
DoCmd.RunCommand acCmdRecordsGoToNew
Me.ProductNumber = TempNbr
Me.ProductName.SetFocus
Exit Sub
End If

End If

Can we assume that Text5 is *unbound*?

Your code needs to:
a) See if the user entered anthing: otherwise the FindFirst string would
be
mal-formed.

b) Explicitly save any edits (as you can't move without saving.)

c) Use a specific kind of recordset (rather than merely Object.)

d) Test NoMatch rather than EOF to see if there was a match.

e) Respond in 2 ways (find, or go new.)

Something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


You might also want to consider the cases where a record exists but might
not be found, e.g. if the form was filtered, or in DataEntry mode.

wow, i'm back for more advice. the problem I have is that after
completing a
search for a specific record and not finding it, how do I get the code
to
advance to a new record instead of staying on the last record in the
record
set. I'm using the following code to look for a record:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductNumber] = " & Str(Nz(Me![Text5]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What I wish to do is give the user the option of re-entering the
product
number and trying again or deciding to enter the product as a new
product.
Thanks for any help that can be given.

 

[The Old Guy]

Quite the contrary, kind sir. I'm new to programming in VBA (prior COBOL) so
I would only guess I didn't recognize the differences they could or would
make. The coding you provided is being used in my program. I thank you for
that. Now, the problem is providing the user with the option of re-entering
the desired product number (to correct for data entry errors) or to add the
product number to the MasterItemTbl, along with all the pertinent data for
that item. I'm assuming it would go where the command to go to a new record
is located, just not sure how to do that. Once the user decided to add the
product, the ProductNumber is automatically recorded and the ProductName
control is given the focus. The second set of coding I provided is my
thoughts on how to accomplish that, however, I haven't tested it (scared I'm
thinking). Once again, I do thank you for your input and comments.

John

The SetFocus won't work if this is the AfterUpdate event of Text5.

Set to Null rather than a zero-length string.

I guess you ignored all the other suggestions I gave, so you are not
interested in the difference they could make.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the reply. yes, text 5 is unbound. do you think the
coding starting at 'response =' would work where the new record command
is?
that has been the real problem area. trying to get it to work based on
what
the user would select.




Dim MsgA, StyleA, TitleA, Response
Dim TempNbr As Single


MsgA = "Item Not Found! Click 'Yes' to add" _
& Chr$(13) & "this item. Click 'NO' to " _
& Chr$(13) & "re-enter the item number."

StyleA = vbYesNo + vbInformation + vbDefaultButton2
TitleA = "Item Data Error"

Response = MsgBox(MsgA, StyleA, TitleA)
If Response = 7 Then
Me.Text5 = ""
DoCmd.RunCommand acCmdRecordsGoToNew
Me.Text5.SetFocus
Exit Sub
Else
TempNbr = Me.Text5
DoCmd.RunCommand acCmdRecordsGoToNew
Me.ProductNumber = TempNbr
Me.ProductName.SetFocus
Exit Sub
End If

End If

Can we assume that Text5 is *unbound*?

Your code needs to:
a) See if the user entered anthing: otherwise the FindFirst string would
be
mal-formed.

b) Explicitly save any edits (as you can't move without saving.)

c) Use a specific kind of recordset (rather than merely Object.)

d) Test NoMatch rather than EOF to see if there was a match.

e) Respond in 2 ways (find, or go new.)

Something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


You might also want to consider the cases where a record exists but might
not be found, e.g. if the form was filtered, or in DataEntry mode.

wow, i'm back for more advice. the problem I have is that after
completing a
search for a specific record and not finding it, how do I get the code
to
advance to a new record instead of staying on the last record in the
record
set. I'm using the following code to look for a record:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductNumber] = " & Str(Nz(Me![Text5]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What I wish to do is give the user the option of re-entering the
product
number and trying again or deciding to enter the product as a new
product.
Thanks for any help that can be given.

 

[Allen Browne]

Have a go!

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
If MsgBox("Create?", vbYesNo) = vbYes Then
Me.ProductNumber = Me.Text5
End If
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
Me.Text5 = Null
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Quite the contrary, kind sir. I'm new to programming in VBA (prior COBOL)
so
I would only guess I didn't recognize the differences they could or would
make. The coding you provided is being used in my program. I thank you
for
that. Now, the problem is providing the user with the option of
re-entering
the desired product number (to correct for data entry errors) or to add
the
product number to the MasterItemTbl, along with all the pertinent data for
that item. I'm assuming it would go where the command to go to a new
record
is located, just not sure how to do that. Once the user decided to add
the
product, the ProductNumber is automatically recorded and the ProductName
control is given the focus. The second set of coding I provided is my
thoughts on how to accomplish that, however, I haven't tested it (scared
I'm
thinking). Once again, I do thank you for your input and comments.

John

The SetFocus won't work if this is the AfterUpdate event of Text5.

Set to Null rather than a zero-length string.

I guess you ignored all the other suggestions I gave, so you are not
interested in the difference they could make.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the reply. yes, text 5 is unbound. do you think the
coding starting at 'response =' would work where the new record command
is?
that has been the real problem area. trying to get it to work based on
what
the user would select.




Dim MsgA, StyleA, TitleA, Response
Dim TempNbr As Single


MsgA = "Item Not Found! Click 'Yes' to add" _
& Chr$(13) & "this item. Click 'NO' to " _
& Chr$(13) & "re-enter the item number."

StyleA = vbYesNo + vbInformation + vbDefaultButton2
TitleA = "Item Data Error"

Response = MsgBox(MsgA, StyleA, TitleA)
If Response = 7 Then
Me.Text5 = ""
DoCmd.RunCommand acCmdRecordsGoToNew
Me.Text5.SetFocus
Exit Sub
Else
TempNbr = Me.Text5
DoCmd.RunCommand acCmdRecordsGoToNew
Me.ProductNumber = TempNbr
Me.ProductName.SetFocus
Exit Sub
End If

End If


:

Can we assume that Text5 is *unbound*?

Your code needs to:
a) See if the user entered anthing: otherwise the FindFirst string
would
be
mal-formed.

b) Explicitly save any edits (as you can't move without saving.)

c) Use a specific kind of recordset (rather than merely Object.)

d) Test NoMatch rather than EOF to see if there was a match.

e) Respond in 2 ways (find, or go new.)

Something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


You might also want to consider the cases where a record exists but
might
not be found, e.g. if the form was filtered, or in DataEntry mode.

wow, i'm back for more advice. the problem I have is that after
completing a
search for a specific record and not finding it, how do I get the
code
to
advance to a new record instead of staying on the last record in the
record
set. I'm using the following code to look for a record:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductNumber] = " & Str(Nz(Me![Text5]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What I wish to do is give the user the option of re-entering the
product
number and trying again or deciding to enter the product as a new
product.
Thanks for any help that can be given.

 

[The Old Guy]

will try. must go to city for treatment. will try this when i recover a
little, probably by friday next. thanks.

Have a go!

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
If MsgBox("Create?", vbYesNo) = vbYes Then
Me.ProductNumber = Me.Text5
End If
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
Me.Text5 = Null
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Quite the contrary, kind sir. I'm new to programming in VBA (prior COBOL)
so
I would only guess I didn't recognize the differences they could or would
make. The coding you provided is being used in my program. I thank you
for
that. Now, the problem is providing the user with the option of
re-entering
the desired product number (to correct for data entry errors) or to add
the
product number to the MasterItemTbl, along with all the pertinent data for
that item. I'm assuming it would go where the command to go to a new
record
is located, just not sure how to do that. Once the user decided to add
the
product, the ProductNumber is automatically recorded and the ProductName
control is given the focus. The second set of coding I provided is my
thoughts on how to accomplish that, however, I haven't tested it (scared
I'm
thinking). Once again, I do thank you for your input and comments.

John

The SetFocus won't work if this is the AfterUpdate event of Text5.

Set to Null rather than a zero-length string.

I guess you ignored all the other suggestions I gave, so you are not
interested in the difference they could make.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the reply. yes, text 5 is unbound. do you think the
coding starting at 'response =' would work where the new record command
is?
that has been the real problem area. trying to get it to work based on
what
the user would select.




Dim MsgA, StyleA, TitleA, Response
Dim TempNbr As Single


MsgA = "Item Not Found! Click 'Yes' to add" _
& Chr$(13) & "this item. Click 'NO' to " _
& Chr$(13) & "re-enter the item number."

StyleA = vbYesNo + vbInformation + vbDefaultButton2
TitleA = "Item Data Error"

Response = MsgBox(MsgA, StyleA, TitleA)
If Response = 7 Then
Me.Text5 = ""
DoCmd.RunCommand acCmdRecordsGoToNew
Me.Text5.SetFocus
Exit Sub
Else
TempNbr = Me.Text5
DoCmd.RunCommand acCmdRecordsGoToNew
Me.ProductNumber = TempNbr
Me.ProductName.SetFocus
Exit Sub
End If

End If


:

Can we assume that Text5 is *unbound*?

Your code needs to:
a) See if the user entered anthing: otherwise the FindFirst string
would
be
mal-formed.

b) Explicitly save any edits (as you can't move without saving.)

c) Use a specific kind of recordset (rather than merely Object.)

d) Test NoMatch rather than EOF to see if there was a match.

e) Respond in 2 ways (find, or go new.)

Something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


You might also want to consider the cases where a record exists but
might
not be found, e.g. if the form was filtered, or in DataEntry mode.

wow, i'm back for more advice. the problem I have is that after
completing a
search for a specific record and not finding it, how do I get the
code
to
advance to a new record instead of staying on the last record in the
record
set. I'm using the following code to look for a record:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductNumber] = " & Str(Nz(Me![Text5]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What I wish to do is give the user the option of re-entering the
product
number and trying again or deciding to enter the product as a new
product.
Thanks for any help that can be given.

 

[The Old Guy]

greetings, Allen,
I'm back in the land of the living and my disease is in remission. thank
goodness for that. anyway, tried the code you provided and it worked really
well. I had to figure out to handle the condition of bypassing the newrecord
command when the program was already at a new record but that wasn't hard.
Thanks for your advice and maybe we will meet again. Cheers! John

will try. must go to city for treatment. will try this when i recover a
little, probably by friday next. thanks.

Have a go!

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
If MsgBox("Create?", vbYesNo) = vbYes Then
Me.ProductNumber = Me.Text5
End If
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
Me.Text5 = Null
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Quite the contrary, kind sir. I'm new to programming in VBA (prior COBOL)
so
I would only guess I didn't recognize the differences they could or would
make. The coding you provided is being used in my program. I thank you
for
that. Now, the problem is providing the user with the option of
re-entering
the desired product number (to correct for data entry errors) or to add
the
product number to the MasterItemTbl, along with all the pertinent data for
that item. I'm assuming it would go where the command to go to a new
record
is located, just not sure how to do that. Once the user decided to add
the
product, the ProductNumber is automatically recorded and the ProductName
control is given the focus. The second set of coding I provided is my
thoughts on how to accomplish that, however, I haven't tested it (scared
I'm
thinking). Once again, I do thank you for your input and comments.

John


:

The SetFocus won't work if this is the AfterUpdate event of Text5.

Set to Null rather than a zero-length string.

I guess you ignored all the other suggestions I gave, so you are not
interested in the difference they could make.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the reply. yes, text 5 is unbound. do you think the
coding starting at 'response =' would work where the new record command
is?
that has been the real problem area. trying to get it to work based on
what
the user would select.




Dim MsgA, StyleA, TitleA, Response
Dim TempNbr As Single


MsgA = "Item Not Found! Click 'Yes' to add" _
& Chr$(13) & "this item. Click 'NO' to " _
& Chr$(13) & "re-enter the item number."

StyleA = vbYesNo + vbInformation + vbDefaultButton2
TitleA = "Item Data Error"

Response = MsgBox(MsgA, StyleA, TitleA)
If Response = 7 Then
Me.Text5 = ""
DoCmd.RunCommand acCmdRecordsGoToNew
Me.Text5.SetFocus
Exit Sub
Else
TempNbr = Me.Text5
DoCmd.RunCommand acCmdRecordsGoToNew
Me.ProductNumber = TempNbr
Me.ProductName.SetFocus
Exit Sub
End If

End If


:

Can we assume that Text5 is *unbound*?

Your code needs to:
a) See if the user entered anthing: otherwise the FindFirst string
would
be
mal-formed.

b) Explicitly save any edits (as you can't move without saving.)

c) Use a specific kind of recordset (rather than merely Object.)

d) Test NoMatch rather than EOF to see if there was a match.

e) Respond in 2 ways (find, or go new.)

Something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.Text5) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "ProductNumber = " & Me.Text5
If rs.NoMatch Then
RunCommand acCmdRecordsGoToNew
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


You might also want to consider the cases where a record exists but
might
not be found, e.g. if the form was filtered, or in DataEntry mode.

wow, i'm back for more advice. the problem I have is that after
completing a
search for a specific record and not finding it, how do I get the
code
to
advance to a new record instead of staying on the last record in the
record
set. I'm using the following code to look for a record:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductNumber] = " & Str(Nz(Me![Text5]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What I wish to do is give the user the option of re-entering the
product
number and trying again or deciding to enter the product as a new
product.
Thanks for any help that can be given.

 

[Allen Browne]

I'm back in the land of the living and my disease is in remission. thank
goodness for that. anyway, tried the code you provided and it worked
really well.

That's really good news, John. Not only on the Access issue, but especially
on your results.

 

[The Old Guy]

Thanks for the kind words. Have a great day.

John