Solved

Form requery not firing current event

Posted on 2003-11-07
6
605 Views
Last Modified: 2008-01-16
Hello,
I have a main form in Access 2000 with a query as a recordsource. The query simply obtains the PK of tblStart (PK is "AS nRespondentUID", which is different than in the table). The main form has several subforms (all 1 to 1), notably a subform based on tblStart. I know that's odd, and I could add another table if necessary but somehow I don't think that's the problem.

I want to have a textbox (Text53) in the main form (default value null) where one can enter a PK value and that will be used to determine the current record. So if the textbox is null (as it will be when the form is first opened) it doesn't open any record; when the textbox matches an existing PK, it goes to that record; and when the textbox doesn't match an existing PK, one is created (using qryNewRowAllQ) then it goes to that newly created record.

Sounds simple but I've had the hardest time. I've gotten a similar thing to work with a combobox.

Using the following code, if I enter a new PK value in the text box, a row is created, then the requery event fires and the number of records in the navigation bar is updated, but the Form_Current() procedure doesn't run. I think it should run, which should then navigate to the appropriate record. But Form_Current doesn't run at all after the requery.

I get a non fatal (meaning that even if I don't trap it nothing stops) "Current error" message from my MsgBox when the form is opened for the first time, but not after the requery.

So after the requery, the textbox appears to hold the new PK value I entered, but the record is still the first record in the form recordsource.

Private Sub Form_Current()
    On Error GoTo ErrorHandler
    MsgBox "Current"
    If Not IsNull([Text53]) Then
        ' Find the record that matches the control.
        Dim rsc As DAO.Recordset
        Set rsc = Me.Recordset.Clone
        rsc.FindFirst "[nRespondentUID] = " & Str(Me![Text53])
        If (Not rsc.NoMatch) Then
            Me.Bookmark = rsc.Bookmark
        End If
        rsc.Close
    Text53.Value = [nRespondentUID]
    End If
    DoCmd.GoToControl "Text53"
    ErrorHandler:
        MsgBox "Current error"
End Sub

Private Sub Text53_AfterUpdate()
    On Error GoTo ErrorHandler
    ' Find the record that matches the control.
    Dim rs As DAO.Recordset
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[nRespondentUID] = " & Str(Me![Text53])
    If (rs.NoMatch) Then
        DoCmd.OpenQuery "qryNewRowAllQ"
        Me.Requery
        rs.FindFirst "[nRespondentUID] = " & Str(Me![Text53])
    End If
    Me.Bookmark = rs.Bookmark
    rs.Close
    ErrorHandler:
        MsgBox "afterupdate error"
End Sub


triplediamond
0
Comment
Question by:triplediamond
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9704209
I'd drop the two events and put the code under the click event of a button, - that'd be more obvious to new users, and for skilledusers you could just place the button after the textbox-control in the tab-sequence so that you could just hit enter twice to execute.

Regards,
Sven
0
 
LVL 2

Expert Comment

by:ericmca
ID: 9704642
Triplediamond,
Are you aware that there is no way around your ErrorHandler code in both your subs?  You will get the error message every time the sub runs (your "nonfatal error").  Better put an Exit Sub before ErrorHandler:

In the Textbox after_update code, I'm not sure what query you are opening when there is a NoMatch.  It seems you are still looking for the UID you couldn't find earlier.  Can you just use a rst.MoveNew here?

-- Eric
0
 
LVL 2

Expert Comment

by:ericmca
ID: 9704705
OOps.  That should be    rst.AddNew
-- Eric
0
 

Author Comment

by:triplediamond
ID: 9718778
Here is the code that works for me. I disabled the navigation buttons because they don't work together with this code. The other comments were helpful, especially the one about Exit Sub, but my solution really has nothing to do with the comments so I'd like to request a refund if no one strongly objects.

Actually I suspect there's a better way to do this and if someone wants to post it here I'd be more than happy to award them the points. It seems like a pretty general purpose problem.

Private Sub Form_Current()
    On Error GoTo ErrorHandler
    'Text53.Value = Str(Nz(Me![nRespondentUID], 0))

    DoCmd.GoToControl "Page1"
    Exit Sub
ErrorHandler:
    MsgBox "current error"
End Sub

Private Sub Text53_AfterUpdate()
    On Error GoTo ErrorHandler
    'Find the record that matches the control.
    Dim rs As DAO.Recordset
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[nRespondentUID] = " & Str(Me![Text53])
    If (rs.NoMatch) Then
        DoCmd.GoToRecord , , acNewRec
        Me![nRespondentUID] = CInt(Nz(Me![Text53], 0))
        DoCmd.OpenQuery "qryNewRowAllQExceptTblQStart"
        Me.Refresh
        Else
            Me.Bookmark = rs.Bookmark
    End If
    rs.Close
    Exit Sub
ErrorHandler:
    MsgBox "afterupdate error"
End Sub


triplediamond

0
 

Accepted Solution

by:
SpazMODic earned 0 total points
ID: 9743494
PAQed, with points refunded (250)

SpazMODic
EE Moderator
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question