Requery a listbox in Main form but stay on current Record

F

Falcon88

Registered User.
Local time
Today, 16:21
Joined
Nov 4, 2014
Messages
318
Hiiii All

I have a Form with Sub form , I add some data to sub form and then need to requery a listbox in Main form.
i try :
Code: 
Dim lngPK As Long 'change data type if yours is different

'set variable to current record ID
lngPK = Me.SerNo
Me.Parent.ListOne.Requery
'Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "SerNo = " & lngPK
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With
SerNo :(a combobox in my sub form) and a second Primary key in my table (show the attached photo please).
SerNo : is unique for this Order.
Problem is that this returns me to first record in the subform, while I want to stay on the current record.

thanks .
 

Attachments

  • tblPhoto.jpg
    tblPhoto.jpg
    14.8 KB · Views: 154
Data is committed to table when: 1. close table/query/form or 2. move to another record or 3. run code to save.

Maybe you just need to run code to save.
DoCmd.RunCommand acCmdSaveRecord
Me.Parent.ListOne.Requery

Or maybe just need to requery the listbox in its GotFocus event.

The real trick is figuring out what event to put code into.
 
Last edited:
Hmm, try...

Code: 
Me.Parent.ListOne.Requery
Forms![MainFormName]![ListOne] = lngPK
 
GinaWhipp said:
Hmm, try...

Code: 
Me.Parent.ListOne.Requery
Forms![MainFormName]!
[ListOne] = lngPK
Click to expand...

please give complete code.

where put:
Code: 
Me.Parent.ListOne.Requery
Forms![MainFormName]!
[ListOne] = lngPK
 
Do you see your old line *Me.Parent.ListOne.Requery*? Just add that second line right under it...
 
What is the Primary Key of the List Box? Is it the same as the one you are requerying?
 
GinaWhipp said:
What is the Primary Key of the List Box? Is it the same as the one you are requerying?
Click to expand...

no
the Primary Key of the List Box (ServiceID) from the another table (in dblclick on it , it runs an append query to append groups of details in the sub from.)
 
Oh, I thought it was to bring the focus to the records you just appended. To stay on the current record use...

Code: 
Dim intCurrentID As Integer   
 
    intCurrentID = Me.CurrentRecord
    Me.Requery
    DoCmd.GoToRecord , , acGoTo, intCurrentID
Should work at the end of your routine.
 
GinaWhipp said:
Oh, I thought it was to bring the focus to the records you just appended. To stay on the current record use...

Code: 
Dim intCurrentID As Integer   
 
    intCurrentID = Me.CurrentRecord
    Me.Requery
    DoCmd.GoToRecord , , acGoTo, intCurrentID
Should work at the end of your routine.
Click to expand...

thanks.

but i don't want to requery my subform, i want to requery the Listbox (LstOne) on the mainform.
 
I need more coffee... sorry about that. Then I don't understand why the first line I gave you did not work. Is not lngPK equal to that of the List Box Primary Key?
 
GinaWhipp said:
I need more coffee... sorry about that. Then I don't understand why the first line I gave you did not work. Is not lngPK equal to that of the List Box Primary Key?
Click to expand...
please give me the complete code ?
 
Code: 
Dim lngPK As Long 'change data type if yours is different

'set variable to current record ID
lngPK = Me.SerNo
Me.Parent.ListOne.Requery
Forms![[COLOR=red][B]MainFormName[/B][/COLOR]]![ListOne] = lngPK
'Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "SerNo = " & lngPK
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With
Make sure to change the name of *MainFormName* to match your Main Form name.
 
GinaWhipp said:
Code: 
Dim lngPK As Long 'change data type if yours is different

'set variable to current record ID
lngPK = Me.SerNo
Me.Parent.ListOne.Requery
Forms![[COLOR=red][B]MainFormName[/B][/COLOR]]!
[ListOne] = lngPK
'Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "SerNo = " & lngPK
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With
Make sure to change the name of *MainFormName* to match your Main Form name.
Click to expand...

Ok that like as understand .
Code: 
Dim lngPK As Long 'change data type if yours is different

'set variable to current record ID
lngPK = Me.SerNo
Me.Parent.ListOne.Requery
Forms![MainFrm]![OrdersSubFrm].Form!
[ListOne] = lngPK
'Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "SerNo  = " & lngPK
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With

this gives me msg: No Records.

then go to first record.
 
Okay, let's switch that around...

Code: 
Dim lngPK As Long 'change data type if yours is different

'set variable to current record ID
lngPK = Me.SerNo
Me.Parent.ListOne.Requery
'Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "SerNo  = " & lngPK
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With
 
Forms![MainFrm]![OrdersSubFrm].Form![ListOne] = lngPK
 
GinaWhipp said:
Okay, let's switch that around...

Code: 
Dim lngPK As Long 'change data type if yours is different

'set variable to current record ID
lngPK = Me.SerNo
Me.Parent.ListOne.Requery
'Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "SerNo  = " & lngPK
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With
 
Forms![MainFrm]![OrdersSubFrm].Form!
[ListOne] = lngPK
Click to expand...

it stay gives me msg: Record Not found
and go to first record.

q: where you want me to put that code?
i put it under after update for the combobox (SerNo)
 
Where's the code that runs the append query? We code move that last line there...
 
under :
Code: 
Private Sub ListOne_DblClick(Cancel As Integer)
[code/]
 
Umm, that is not the code that runs the append queries or are you saying you're using a Macro?
 
You must log in or register to reply here.

Similar threads

D
Subform requery is starting a new record
Replies
4
Views
752
dullster
D
BlueSpruce
Solved Highlighting Next Record After Current Record Filtered Out of Datasheet
2
Replies
33
Views
1,691
BlueSpruce
BlueSpruce
J
Refreshing List Box
Replies
8
Views
789
JamesJoey
J
S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
1,011
SkeletorPlus
S
C
Solved No Current Record on Access Close
Replies
11
Views
1,380
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom