UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Navigate Form Records Using Command Buttons, Access 2010    
 
   
njdaisy86
post May 15 2018, 12:56 PM
Post#1



Posts: 30
Joined: 18-October 17



theDBguy,

I'm asking for help again. I have a similar issue as the one in this discussion.

I have a Main Form (frm_FindTransferRecord) and a Sub Form (frm_TransferSubform), both are in the details section of form. This form is linked to a query (qry_FindTransferRecords).

I created "Previous Record" and "Next Record" buttons (in the Details section of the form). Currently, they function to scroll through all the subform records before jumping to the next Main Form record. I need the buttons to only scroll through the Main Records produced from the query.

The current code I have on the "On Click" Event for the "Next Record" button is:

Private Sub Command43_Click()
On Error Resume Next
[Forms]![frm_FindTransferRecord.EmployeeID].SetFocus
DoCmd.GoToRecord, , acNext

that code scrolls through the query results sub-records. In other words, if there are three sub-records, it will scroll through all three records before moving to the next Main form record. I hope that makes sense. Thank you again.
This post has been edited by theDBguy: May 15 2018, 01:03 PM
Reason for edit: Split topic from original thread
Go to the top of the page
 
theDBguy
post May 15 2018, 01:01 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,257
Joined: 19-June 07
From: SunnySandyEggo


Hi,

To make sure I understand your form's setup, are you basically saying you have three forms? One form with two subforms in it and you did not put the buttons in either subforms, correct?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
njdaisy86
post May 15 2018, 02:31 PM
Post#3



Posts: 30
Joined: 18-October 17



Sorry no, I did not explain that correctly.

I have a main form, with one sub-form. Based on the results of the query, I need the "Last Record" and "Next Record" buttons to scroll through the Main Form records (which will display the sub form records).

From the picture I attached, the buttons scroll through each sub-form record before it changes to the next Main Form record.

I hope that makes better sense.

Thanks for responding right away.
Attached File(s)
Attached File  Capture.PNG ( 30.95K )Number of downloads: 13
 
Go to the top of the page
 
theDBguy
post May 15 2018, 02:50 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,257
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks for the clarification. For the "Last Record" button, try the following:

DoCmd.GoToRecord , , acLast

You could try showing the Navigation Bar for now while testing the code so you can see if the record pointer is moving properly.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Sweetu
post May 16 2018, 05:56 AM
Post#5



Posts: 103
Joined: 20-September 16



Supposing your Button names are as Cmdlast(to goto Last Record) and CmdNext(to goto next record)

CODE
Private Sub CmdLast_Click()
DoCmd.GoToRecord , , acLast
End Sub

CODE
Private Sub CmdNext_Click()
DoCmd.GoToRecord , , acNext
End Sub



Go to the top of the page
 
missinglinq
post May 16 2018, 08:06 AM
Post#6



Posts: 4,537
Joined: 11-November 02



It sounds as if frm_FindTransferRecord is the Form the Subform is based on...is that correct? I suspect that since you're moving Focus to the Subform Control, Access is then proceeding to navigate thru the Subform Records, rather than the Man Form Records, as you want.

The codes you've been given by theDBguy and Sweetu, placed on the Main Form, should work. But the code for going to the 'next Record' generally needs to check so that when you reach the last Record, you won't be moved to a New Record...or if the AllowedAdditions Property is set to No, so that you won't pop an error.

Here's some boilerplate code I use for custom navigation buttons.

CODE
Private Sub First_Click()
  DoCmd.GoToRecord , , acFirst
End Sub

Private Sub Last_Click()
    DoCmd.GoToRecord , , acLast
End Sub

Private Sub Next_Click()
  If CurrentRecord = RecordsetClone.RecordCount Then
    MsgBox "You are on the Last Record!"
  Else
    DoCmd.GoToRecord , , acNext
  End If
End Sub

Private Sub Previous_Click()
  If CurrentRecord = 1 Then
    MsgBox "You are on the First Record!"
  Else
   DoCmd.GoToRecord , , acPrevious
  End If
End Sub


Linq ;0)>


--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
zaxbat
post May 16 2018, 08:38 AM
Post#7



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Do you see what is happening....since you do not explicitly tell Access on which form you want to move through records...it seems to ignore your setfocus directive and just use the form it decides you want to move through. I am wondering why. Ah, maybe is the problem...you said the main form has a query for its source...the query if to get transfer records. But you do not state your table relationship. Do you have header records for the transfers in one table and then have detail records in a table linked to the header table? If not, that could be your problem. If your DB is currently set up this way, then we are missing something else. You could change the code to explicitly work on only the main forms recordset but even that may not fix your problem if your subform's source is wrong.
This post has been edited by zaxbat: May 16 2018, 08:39 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
njdaisy86
post May 16 2018, 10:30 AM
Post#8



Posts: 30
Joined: 18-October 17



Thank you all (theDBguy, zaxbat, missinglinq, sweetu) for responding. I've tried all your codes, these are the results:

acLast - will advance the Main Form records (without going through all the subform records) but it will just advance to the last main form record and will skip all the records in between the first and last record. So if I have 4 records with the last name of Smith: Smith1 will display, hit "next record, Smith4 will display. (skipping over Smith 2 & 3)

acNext - will advance the Main Form record but only after it goes through all the subform records.

acPrevious - does the same in reverse.

How do I change the focus to the MainForm records? That Table Relationship is one-to-many: tbl_Employee (main) to tbl_Transfer (child). joined by EmployeeID
tbl_Employee (EmployeeID, LastName, FirstName,MiddleInitial, EmployeeType)
tb_Transfer (TranfserID, EmployeeID, TransferDate, TransferType, TransferFrom, TransferFromSquad, TransferTo,TransferFromSquad, Memo)

The qry_FindTransferRecords finds the records for this form frm_TransferRecord by LastName and FirstName fields.

I hope all that information helps. Thank you.
Go to the top of the page
 
Sweetu
post May 16 2018, 03:47 PM
Post#9



Posts: 103
Joined: 20-September 16



is this possible for you to attach the database only the said data & form please ?

are you referring subform controls ? from main form ? what's common between these forms ?which controls?
This post has been edited by Sweetu: May 16 2018, 03:57 PM
Go to the top of the page
 
zaxbat
post May 16 2018, 06:07 PM
Post#10



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


QUOTE
[Forms]![frm_FindTransferRecord.EmployeeID].SetFocus



is this setfocus putting the focus on the detail records or onto the main/parent/header records??????

If it is putting the focus on the details...then this is quite likely your problem.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
njdaisy86
post May 18 2018, 09:50 AM
Post#11



Posts: 30
Joined: 18-October 17



Thank you all for responding.

Zaxbat - you are correct, the focus is being placed on the details, not the main/parent/header records (lLastName.....). This is where I think the focus should be with the nav buttons. I put your line of code (see below) but I received an error:

Private Sub Next_Click()

[Forms]![frm_FindTransferRecord.EmployeeID].SetFocus
If CurrentRecord = RecordsetClone.RecordCount Then
MsgBox "You are on the Last Record!"
Else
DoCmd.GoToRecord , , acNext
End If

As I am inexperienced with VB, can you assist? Thank you.

Sweetu - I really wish I could post my database here but unfortunately it is on a separate secure company system that prevents me from doing so. I'm sorry!
Go to the top of the page
 
zaxbat
post May 18 2018, 11:54 AM
Post#12



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


At this point I am unclear on the names of the forms and which is main and which is subform. Basically, you need to set the focus to the main form's datasheet before trying to do the movenext OR you can explicitly name the mainforms's recordset in the movenext command and that should probably work too (and most likely put the focus there as a side effect of the process---though at that point it won't matter).

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 05:11 AM