Subform control via main form (or better option)

[dwnocturnal]

First post, tried to read up on any rules, tried to search forums before posting...

Fairly new to databases/access, have basic programming knowledge, work in IT field doing networking for a living.

I'l try to keep this short but detailed:

Trying to create a form with a Date field on it that is unbound and allows user input. Entering data in this field runs a query on a table and populates a subform on the main form (fields are date, time, original date, & log). The subform is setup to display as a single form view.



The way I've got this setup, everything works, however...I want to be able to create buttons that will allow the user to click forward/back through the records. I've even got this part to work by having it add/minus 1 to the current date in the Date field on the main form using the on_click action...

Here is the problem... I have multiple records with the same Date (primary key is an autonumber id) and I want the navigation buttons to navigate through the records by their consecutive ID's (not by date). I suppose I could include the ID field on the form and hide it from the user, but I would have to have it populate with the current record ID number. I've tried using the standard wizard buttons for record navigation and they don't work (they dont populate the subform).

I'm hoping I provided all of the necessary info here, but let me know if you need anything else (specific query info, etc). If need be, I can zip/email copy of db (although I'm ashamed of the syntax as it doesn't really follow guidelines).

Thanks in advance for the help guys!

[hertfordkc]

Continuous form or datasheet view on the subform won't work for you?

[dwnocturnal]

Unfortunately not. This has to be "dumbed down" for users; the continuous form will confuse them and the datasheet view doesn't display the data in a usable format.

[RayMilhon]

I may be misreading this but it sounds like you have the navigation buttons on the main form put them on the subform.

[dwnocturnal]

I may be misreading this but it sounds like you have the navigation buttons on the main form put them on the subform.

Sorry, I don't follow your response. To clarify, if it helps, I want to put the buttons on the main form, be able to navigate through the records, and have it update all fields in the main form and the subform to the current record (but the user must have the option to input the Date into the main form field as well).

[RayMilhon]

Just to be sure I understand what your trying to accomplish you stated: "Trying to create a form with a Date field on it that is unbound and allows user input. Entering data in this field runs a query on a table and populates a subform on the main form (fields are date, time, original date, & log). The subform is setup to display as a single form view."

Based on that statement you have a main form with 1 Unbound control that allows the user to input a date. When the date is entered the subform displays the data for that date which can be multiple records you want displayed 1 at a time that the user navigates through. So again the navigation is on the subform not the main form.

Maybe you should email me the database so I can understand what you're trying to do. RayMilhon@hotmail.com

[hertfordkc]

Found this in a Google on "VBA next record".

DoCmd.GoToRecord , , acNext

I suspect that has to be executed from within the subform, which may create another problem for you. I've used recordset manipulators to do rs.First, rs.Last, rs.Next but not sure that would work in your situation.

[dwnocturnal]

Thanks guys for the quick responses.

RayMilhon: I emailed you a copy of a the database with a few details.

hertfordkc: I believe I saw that same information you were looking at, and after trying it several different ways, couldn't get it to perform as desired.

[boblarson]

All you need to do is use a Select Statement as the form's Record Source (instead of the Table itself) and have an ORDER BY clause in that select statement which orders it by ID.

[dwnocturnal]

All you need to do is use a Select Statement as the form's Record Source (instead of the Table itself) and have an ORDER BY clause in that select statement which orders it by ID.

Thanks, Bob. I am currently using a query with a Select statement as the source, but it currently doesn't contain the ID field. As I stated before, I thought about adding this and hiding the field on the form, but I'm still not sure how I would setup the code for the navigational buttons. Also, I'm still fairly new to the queries; I'll see if I can't research a bit and find the syntax to include the ORDER BY in my statement.

[boblarson]

You don't need to add the field to sort on it. If it is in the table then you can include an ORDER BY ID in it if you are typing the SQL manually or if you have the QBE grid you would select that field and just uncheck the SHOW checkbox.

[dwnocturnal]

Bob,

I tried that, but couldn't get it to populate the fields with the correct record when trying the standard navigation buttons. I'll also need the user to be able to input the Date into the main form to populate the subform with the first matching record for that date; will you solution work for this as well?
Thanks.

[boblarson]

I guess I'm not understanding your setup. The subform record source would have the fields in the underlying query/SQL statement and if you sort by ID you can sort by ID and then by DATE.

Is there a field that the master/child links are set to between the main form and the subform? How exactly does the main form relate to the subform?

You might upload a few screenshots here (not on another public sharing site as those are blocked by my workplace) so that we can see what you are actually trying to describe.

[dwnocturnal]

Just got slammed at the office....will try to get some more info/pics for you asap...thx

[dwnocturnal]

Thanks for the help guys, I am going to have to come back to this at a later date. For now I'll mark this as solved.

Thanks