That sSleep worked to get the record into the form's record set. However it
still didn't open to the record that was just entered. It appears to still be
skipping the Me.Bookmark line. The SSN that we are searching is a text field
if that makes any difference.
:
Sounds like a timing issue, then. It appears what is happening is the record
is not getting to the table before the Requery is executed. So, what we can
do to verify this is use a "Sleep" function to make the code pause after the
Execute and before the requery. Copy the code from this site:
http://www.mvps.org/access/api/api0021.htm
Paste it into a standard module by itself.
The put this right before the Me.Requery
sSleep(1000)
That will pause the code for 1 second. That should be more than enough. If
that cures the problem, reduce the sleep time to 500 and see if it still
works. You don't want to slow it down any more than necessary, but you do
need to allow time for the record to get in the database.
It also may be because the Execute bypasses the Access User Interface, the
code is roaring past it (still the timing issue), you could use a different
approach that will stay within the UI and the Me.Requery wont happen until
the other code is complete.
That would be adding
Dim rst As DAO.Recordset to your dim statments. then using this instead of
the Execute:
strSQL = "INSERT INTO tblVeteran ( VetSSN )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Set rst = Currentdb.OpenRecordset("tblVeteran", dbOpenDynaset)
With rst
.AddNew
!VetSSN = NewData
.Update
.Close
End With
Set rst = Nothing
Me.Requery
Leave out the
strSQL and Execute lines.
--
Dave Hargis, Microsoft Access MVP
:
I am learning a bunch and I appreciate you patience with me on this.
.FindFirst isn't finding the record for some reason it is skipping the
Me.Bookmark line. Also after the Me.Requery the record is in the table,
however it is not in the recordset of the form when the code was left the
original way. When I delete the open form and then open the form manually
after the requery it is in the recordset.
thanks again
:
First, I forgot to tell you to remove the OpenForm line. The new record
hasn't been created yet, so it is not useful at the moment.
This line:
Response = acDataErrAdded
Supresses the Access error message and tells Access you have added the data
to the list.
The Me.Requery line should include the record added with the with the
Execute method to the form's recordset. At this point, you should see if it
is in the underlying table and if it is in the recordset. You should also
check to see if it is in the combo's list. It may be it is not in the combo
list and you need to add a line after the Me.Requery to add it to the combo.
Shouldn't be necessary, but you could try it.
Me.cboVetSearch.Requery
There are a couple of ways to determine the results of the .FindFirst.
One is to contine tracing the code. If it goes to the Me.Bookmark line, if
found it.
Or you can use the immediate window
? .NoMatch
If you get True, it didn't find the record.
Look at this as a learning experience. You are learning to use Debug. It
will do you a world of good in the future. <g>
--
Dave Hargis, Microsoft Access MVP
:
Ok so far what I am seeing is it doesn't find the record the user say to
enter a new record.
' This line clears my combo box
Me.cboVetSearch.Undo
'This line opens the form without the new record in the table yet
DoCmd.OpenForm "frmEnterVet"
'This line inserts the new record
strSQL = "INSERT INTO tblVeteran ( VetSSN )SELECT """ & NewData & """ AS
Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
' Not sure what this line does
Response = acDataErrAdded
'This line should refresh the for correct? If so it is not doing it.
Me.Requery
I am also not sure how to check if .FindFirst is finding the record. Would I
look that up in the immediate window?
Thanks,
Patrick
:
Open the form in design mode.
Open the VB Editor to the procedure where the code is.
put your cursor on the first executable line of code in the procedure.
Press F9. The line will change color.
Change the form to Form View.
Enter a Value in the combo that is not in the table.
When you press tab or enter, the VB Editor will open with the line
highlighted.
That line has not yet executed. Press F8 to execute it.
You can walk through the code one line at a time.
There are several ways to look at what is going on. For variables and field
names, you can hover the mouse over them and you will get a box like a tool
tip that will show the value. You can also use the VBE immediate window
(<ctrl>G) and type in values to look at. For example:
? Me.MyCombo
Will return the value of the control.
You can also create a Watch window that will display the value of objects
and properties. You will find that in the VBE Debug menu.
You can also pull up the database window and view objects there. This would
be useful to see if the new record is in the table when you expect it. Just
open the table and look for the record.
There is a lot you can do while debugging. Look at the options in the Debug
menu. The Set Next Statment option is great because you can change values of
variables or controls and go back and re execute a line. There are also
options that let you bypass debug mode for a called function. For example if
you have a function you are certain is working correctly and want to just let
it run so you can get on to the next line in the current code, you can do
that.
--
Dave Hargis, Microsoft Access MVP
:
I am unfamiliar with how to run the code in debug mode. Could you give me a
quick idea how to do it or a link with an example of what to do? Thanks for
the continued support.
:
No problem, No toes stepped on and no offense taken. I was just concerned
what I had posted wasn't working for you and I wanted to be sure you got what
you needed. Is it still not moving to the newly added record?
This part of the code should be doing that.
'This line is necessary to include the new record added to the table in the
form's recordset
Me.Requery
With Me.RecordsetClone
'This line searches for the record with the SSN being the newly added
record. NewData should be the value entered in the text box
.FindFirst "[VetSSN] = """ & NewData & """"
'This line just checks to make sure the value was found so trying to set the
bookmark won't throw an error
If Not .NoMatch Then
'This line positions the form's recordset to the bookmark of the
recordsetclone and makes it the current record
Me.Bookmark = .Bookmark
End If
End With
If that is not working correctly, let me know, please. Also, have you run
the code in debug mode to see if NewData is the expected value and whether
the FindFirst is finding the record. Also, put a breakpoint on the line
after the Execute statment and look in the table to be sure the record has
been added. If it has, break after the requery and see if it is in the
form's recordset.
--
Dave Hargis, Microsoft Access MVP
:
Klatuu & Beetle,
I hope I didn't step on any toes. I wasn't thinking of them as the same
problem when obviously they were. Klatuu your code works great. User gets a
prompt clicks on enter a new record and the form comes up. However it is
coming up with the first record in the table and not the new VetSSN that was
just inserted in the table. I have reduced the code back to what you gave me
and it now looks as follows.
Dim strSQL As String
If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then
Me.cboVetSearch.Undo
DoCmd.OpenForm "frmEnterVet"
strSQL = "INSERT INTO tblVeteran ( VetSSN )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[VetSSN] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboVetSearch.Undo
Response = acDataErrContinue
End If
Thanks
:
Arghh! I hate computers (but, if it weren't for computers I would have to get
a real job)
Anyway, I read the original post and it was not clear. The code takes him
to the record with just the SSN so he can complete the other info. So, I was
puzzled about what he was trying to do.
--
Dave Hargis, Microsoft Access MVP
:
Hi Dave
Sorry, didn't mean to step on any toes. I knew it was your code, but he hadn't
had any responses so I thought I would try to help.
As I understand it the OP wants to open a second form, "frmEnterVet",
already displaying the new VetSSN, so the users can enter the rest of
the info related to that new value. As the code was written, it appeared that
the second form was being opened before the new value was commited,
so that's why I suggested he move the OpenForm further down in the code
(after the new value is committed and the first form requeried/bookmarked)
--
_________
Sean Bailey
:
I originally wrote that code and posted it for your. As written (without the
OpenForm), it should go to the new record. Is it not doing that?
What you now have is a mixture of two methods of doing the same thing and it
will not work that way.
--
Dave Hargis, Microsoft Access MVP
:
I have a main form where users can search for a record if there is no record
it prompts the user to enter a new record. If the user selects yes a form
opens for them to enter the new record. What I want to do is have it so that
the form opens to the new record. Here is my code. This is in the Not in List
event for cboVetSearch
Dim strSQL As String
If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then
