Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to point to recently Added record in Continuous form from Entry form

Posted on 2008-10-07
17
Medium Priority
?
245 Views
Last Modified: 2008-10-07
I have a continuous form that the user can select a record and click on Edit or Add and it will open an Entry form.

If the record is edited, once the entry form is closed, the pointer will be on the record worked on.

If a record is added, once the entry form is closed, the pointer will be on the first record on the continuous form.

How can I make it point to the new record in the continuous form ?

The following is my code:
Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
'save

Dim intIDSelected As Integer

'new
If IsNull(Forms![frmbuyersowners]![id]) Then
   intIDSelected = 0
Else
    intIDSelected = Forms![frmbuyersowners]![id]
End If

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

' this points to the edited record
If intIDSelected <> Me.id Then
    Forms![frmbuyersowners].Requery
End If

Exit_Command28_Click:
    Exit Sub

Err_Command28_Click:
    MsgBox Err.Description
    Resume Exit_Command28_Click
   
End Sub
0
Comment
Question by:proffittware
[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
  • 12
  • 5
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22665403
how do you open the Entry Form? postthe codes here
0
 

Author Comment

by:proffittware
ID: 22665455
For the EDIT:
Private Sub Command37_Click()
On Error GoTo Err_Command37_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmBuyersOwners AE"
   
    stLinkCriteria = "[id]=" & Me![id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command37_Click:
    Exit Sub

Err_Command37_Click:
    MsgBox Err.Description
    Resume Exit_Command37_Click
   
End Sub

For the ADD:
Private Sub cmdAddBuyer_Click()
On Error GoTo Err_cmdAddBuyer_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmBuyersOwners AE"
   
    DoCmd.OpenForm stDocName, , , , acFormAdd

'    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec
   
Exit_cmdAddBuyer_Click:
    Exit Sub

Err_cmdAddBuyer_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddBuyer_Click
   
End Sub

Once the Entry Screen opens, then they hit an ADD button:
Private Sub Command36_Click()
On Error GoTo Err_Command36_Click
'add

    Dim stDocName As String

    stDocName = "Add Buyer to Buyer AE"
    DoCmd.RunMacro stDocName
' this macro setsvalues for basic information - year, location that are generic.

    Me.Refresh
   
Exit_Command36_Click:
    Exit Sub

Err_Command36_Click:
    MsgBox Err.Description
    Resume Exit_Command36_Click
   
End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22665472
what about the record Id, is it autonumber? what is the name of the control in form "frmBuyersOwners AE"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:proffittware
ID: 22665514
ID is autonumber.

Name of Control ?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22665536
name of the textbox that holds the ID?
0
 

Author Comment

by:proffittware
ID: 22665553
ID
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22665615
you will need to declare a global variable in a regular module

option compare database
option explicit

global newRecordID as long




in form "frmBuyersOwners AE"

Private Sub Command36_Click()
On Error GoTo Err_Command36_Click
'add

    Dim stDocName As String

    stDocName = "Add Buyer to Buyer AE"
    DoCmd.RunMacro stDocName
' this macro setsvalues for basic information - year, location that are generic.

'pass the value of the new ID to the global variable newRecordID
 
newRecordID=me.id

    Me.Refresh
   
Exit_Command36_Click:
    Exit Sub

Err_Command36_Click:
    MsgBox Err.Description
    Resume Exit_Command36_Click
   
End Sub




Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
'save

Dim intIDSelected As Integer

'new
If IsNull(Forms![frmbuyersowners]![id]) Then
   intIDSelected = 0
Else
    intIDSelected = Forms![frmbuyersowners]![id]
End If

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

' this points to the edited record
If intIDSelected <> Me.id Then
    Forms![frmbuyersowners].Requery

'Add these codes
    with me.recordsetclone
            .findfirst "[ID]=" & newRecorID
            if not .nomatch then me.bookmark=.bookmark
    end with



End If

Exit_Command28_Click:
    Exit Sub

Err_Command28_Click:
    MsgBox Err.Description
    Resume Exit_Command28_Click
   
End Sub

0
 

Author Comment

by:proffittware
ID: 22665641
I will try and let you know.
0
 

Author Comment

by:proffittware
ID: 22665729
Add your suggested code and the results did not change.

If I EDIT the record, then Exit, it will point to the record that was selected in the continuous form.

Adding one or more records, the pointer on the continuous form went to the first record.
0
 

Author Comment

by:proffittware
ID: 22665837
If I take out ...
    Forms![frmbuyersowners].Requery

Then the record pointer will point to the record EDITed and the record ADDED....but the ADD record does not appear in the ORDER BY order.

I have not tried more than one ADDed record which I will do now.
0
 

Author Comment

by:proffittware
ID: 22665861
One Edited record and two added records...it is pointing to the edited record when you exit the Entry form. The records are not in order on the continuous form....but if you exit the continuous and open it up again, the records are in order.

I do have a query in the record source of the continuous form  to put the records in Order by.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 22665879
use this codes




 
For the ADD:
Private Sub cmdAddBuyer_Click()
On Error GoTo Err_cmdAddBuyer_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmBuyersOwners AE"
   
    DoCmd.OpenForm stDocName, , , , acFormAdd, acdialog
    
'requery the form
    me.requery
 
'Add these codes
    with me.recordsetclone
            .findfirst "[ID]=" & newRecorID
            if not .nomatch then me.bookmark=.bookmark
    end with
 
 
Exit_cmdAddBuyer_Click:
    Exit Sub
 
Err_cmdAddBuyer_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddBuyer_Click
   
End Sub
 
 
Once the Entry Screen opens, then they hit an ADD button:
Private Sub Command36_Click()
On Error GoTo Err_Command36_Click
'add
 
    Dim stDocName As String
 
    stDocName = "Add Buyer to Buyer AE"
    DoCmd.RunMacro stDocName
' this macro setsvalues for basic information - year, location that are generic.
 
    Me.Refresh
 
'pass the value of the new ID to the global variable newRecordID
 
    newRecordID=me.id
 
 
   
Exit_Command36_Click:
    Exit Sub
 
Err_Command36_Click:
    MsgBox Err.Description
    Resume Exit_Command36_Click
 
End Sub

Open in new window

0
 

Author Comment

by:proffittware
ID: 22665884
Need to correct the comment above.
****
Then the record pointer will point to the record EDITed and the record ADDED....but the ADD record does not appear in the ORDER BY order.
****
Then the record pointer will point to the record EDITed. The ADDed record does not appear in the ORDER BY order.

0
 

Author Comment

by:proffittware
ID: 22665921
I will try your new code and get back with you...but what if they hit the EDIT button first, then the ADD button. Does that change things ?
0
 

Author Comment

by:proffittware
ID: 22666187
I called the user and they said they are first editing a record from the Entry screen, then they are adding (hitting the Add button) from the same screen.

The conditions I described are Edit first, then Add.

I am still trying the code for the Add directly from the continuous form.

Get back shortly.
0
 

Author Comment

by:proffittware
ID: 22666407
Well...this took me longer than I thought....

but still the same results...the continuous form does not show the new record in the order it should appear. I can click on A-Z and it appear in the correct order.

0
 

Author Comment

by:proffittware
ID: 22666619
I added

newrecordid = me.id

after my DoCmd for my save.

and it worked the way they wanted.  Thanks...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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