Requery a listbox in Main form but stay on current Record (1 Viewer)

F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
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: 88
June7

June7

AWF VIP
Local time
Today, 08:31
Joined
Mar 9, 2014
Messages
5,464
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:
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
Hmm, try...

Code: 
Me.Parent.ListOne.Requery
Forms![MainFormName]![ListOne] = lngPK
 
F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
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
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
Do you see your old line *Me.Parent.ListOne.Requery*? Just add that second line right under it...
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
What is the Primary Key of the List Box? Is it the same as the one you are requerying?
 
F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
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.)
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
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.
 
F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
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.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
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?
 
F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
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 ?
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
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.
 
F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
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.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
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
 
F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
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)
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
Where's the code that runs the append query? We code move that last line there...
 
F

Falcon88

Registered User.
Local time
Today, 19:31
Joined
Nov 4, 2014
Messages
299
under :
Code: 
Private Sub ListOne_DblClick(Cancel As Integer)
[code/]
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 12:31
Joined
Jun 21, 2011
Messages
5,900
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
Delete form record after successful transaction fails at second attempt
Replies
10
Views
407
Daniela Z.
D
D
Requery Current Combobox in Continuous Form
Replies
3
Views
383
MajP
MajP
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
410
gemma-the-husky
gemma-the-husky
F
Solved Switch between form and datasheet view and stay on current record
Replies
2
Views
701
frankt68
F
M
Solved Refresh listbox on another form
Replies
16
Views
1,302
mafhobb
M

Users who are viewing this thread

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