How do I control Record Selection on a Sub-Form?

[hubs]

Hi! I am new to Access and would appreciate some help.

I am develping a browser form which provides the user a view of some data (i.e. surname, firstname, dob) from a database using a query. I am trying to replace the standard system navigation bar with my own buttons which allow the user to click on it and move the record selection to "next" or "previous". I don't know how to attatch an image file to this message which would better explain what I am doing. Eventually I want to allow the user to select a delete button to "deactivate " a record (eg if a customer moves over seas), or type in a surname in a field which dynamically filters the query with the letters entered in the field, and to allow the user to double-click the required record which opens up a new form containing all the other information about the customer. Adding a dynamic search capability is also something I would like to include.

I am only practising with a small test example with the code as follows, simply to get the record selection to move with the click of a button:

Option Compare Database

Private Sub BtnNextRecord_Click()
Dim Browser As Form
Dim rstBrowser As DAO.Recordset

   Set Browser = Me.Contacts_Query_subform.Form
   rstBrowser = Browser.Recordset
   rstBrowser.MoveNext
   
   
End Sub

I hope this makes some sense to someone and once I have the fully functional browser working I will post it for others to take advantage of.

[Scott Price]

After reading your thread several times, I'm not quite sure what you specific question is!

However, this is a link to a Find As You Type utility that may help you in your dynamic search situation.

Welcome to the Scripts!

Regards,
Scott

[hubs]

Scott,

Thanks for the reply and I am sorry for the confusing post. I will break my problem down and focus on one issue at a time (having now re-read the guidelines).

Using Access 2003 on XP, I have created a form which includes a subform which is a datasheet using a query as its data source.

I am not using the form/subform relationship as normal (eg customer details on the form and order details in the subform). I have used the subform to list some of a customers details (i.e. surname, firstname, dob). Using the subform to list the customer details allows me to add control buttons on the form which I want to use to control the navigation or selection of records on the subform (datsheet).

That is, I want to use my own control buttons to navigate the subform (customers datasheet), and not use the standard navigation tool.

I notice on the message posting toolbar an insert image button, however, I don't know to use this, but getting you a coy of my form would more clearly explain what I am trying to achieve.

Finally, thankyou for your link to dynamic filtering. This will be very helpful when I get to that aspect of my development.

Thanks again

Peter

[Scott Price]

Hi Peter,

Does the code that you posted not work? And if not, what error message(s) does it give?

Good eve,

Scott

[hubs]

Scott,

Sorry, no the code (above) does not work. I receive the following Microsoft Visual Basic error:

Compile error: Invalid use of property

Regards

Peter

[Scott Price]

Try using the Set method for the line of code that gives you that error message... I.e

Set rstBrowser = Browser.Recordset

.

Regards,
Scott

[hubs]

Scott,

Sorry for taking so long replying - out of the office and weekend.

Thankyou very much for the solution, it worked a treat.

Regards

Peter

[Jim Doherty]

Scott,

Thanks for the reply and I am sorry for the confusing post. I will break my problem down and focus on one issue at a time (having now re-read the guidelines).

Using Access 2003 on XP, I have created a form which includes a subform which is a datasheet using a query as its data source.

I am not using the form/subform relationship as normal (eg customer details on the form and order details in the subform). I have used the subform to list some of a customers details (i.e. surname, firstname, dob). Using the subform to list the customer details allows me to add control buttons on the form which I want to use to control the navigation or selection of records on the subform (datsheet).

That is, I want to use my own control buttons to navigate the subform (customers datasheet), and not use the standard navigation tool.

I notice on the message posting toolbar an insert image button, however, I don't know to use this, but getting you a coy of my form would more clearly explain what I am trying to achieve.

Finally, thankyou for your link to dynamic filtering. This will be very helpful when I get to that aspect of my development.

Thanks again

Peter

Peter,

In addition to Scotts advice as an aide to the principle of this thread if you PM me (private message) I will send you a database I knocked up for newbies that demonstrates technique how to search multiple fields in combination within the environment of a main form and a subform using minimal code that builds the where clause of an SQL statement dynamically. Included within that is how to dynamically set a reports recordsource based on your search return and an also the opening of a separate popup form to show the main data of the record selected in the subform.

The only thing it doesnt have presently are the custom navigation buttons but that is relatively easy thing to incorporate and I may include that into the subform elements if thats what you need to proceed.

There is also an output to excel of the same dataset where it turns on excel column headers so that you can deal with the data externally if you wish also tab page useage, listbox referencing and adding records from the same unbound main form where a record might not exist.

I'm building this thing incrementally as a learning reference for newbs much as anything else.

Regards

Jim :)