using requery (1 Viewer)

[mcalex]

Hi all

How do I do this? I want to have a form that opens with Author info. On the form is a table with books by the author (book title, year, publisher), and when the user selects a book in the table and clicks a 'Show Detail' button, a subform becomes visible with detailed information about that book (genre, plot synopsis, characters, notes etc).

What I've done
I have a form with two subforms. Subform1 is a datasheet view (table) of the selected columns. Subform2 has controls to show one one book and is initially non-Visible. I set the RecordSource for Subform2 in the (sub)Form_Open event using a getBookId function to identify the correct book, but, because subforms open before their forms, the initial record to be displayed is set to 0. This is reasonable at Form open time. When a different book is selected in the table, the setBookId function is invoked by the table's (actually the Form's) _Current method and Subform2 is Requery'ed. However, debug.print shows that the RecordSource sql is still querying where BookId = 0 (ie the initial value), and not the value returned by getBookId.

Does the record source sql get 'inline'd when declared from Form_Open in a subform? How do I change the sql to query a different record except through a variable and getters and setters. Should I dim a local copy of BookId in the subform, and have the query use that instead of calling getBookId (which is in a separate module)? I'm a bit confused.

tia
mcalex

 

[SteveSchapel]

Mcalex,

I don't kinow what this getBookId function does. But I can suggest an easier way.

On the main form, put an unbound textbox, and set its Control Source to the equivalent of this:
=[Subform1]![BookId]

Let's suppose yu name this textbox BookRef.

Then, look at the Properties of Subform2. Set the Link Child Fields property to [BookId], and the Link Master Fields property to [BookRef].

Then, as you move from one record to another on Subform1, the details of the current book will be automatically updated in Subform2.

Any good?

 

[mcalex]

Hi Steve

thanks for your help, and yes it looks much simpler. I get what you're doing, but I fell over setting the Master and Detail fields with a 'Can't build a link between unbound forms' error.

How do I link the forms? I thought subforms were automatically linked to the main form. Googling 'link forms in access' isn't helping

thx
mcalex

PS: getBookID & setBookID are simple public methods that either return the BookID variable or change it. The two methods and the variable are all declared in a separate (non-class) module for code-reuse & stuff. I'm from java.

 

[SteveSchapel]

Mcalex,

I am not familiar with the 'Can't build a link between unbound forms' error. I know the subform is not unbound. Is the main form unbound? If so, and if that is causing Access to object, then I confess I was not aware of this restriction - I guess it is very unusual to have an unbound form in this scenario. Well, bind it.

 

[mcalex]

'bind' means give it a control source in the properties box (rather than in the Form_Open event, like I'm doing) doesn't it?

 

[SteveSchapel]

Mcalex,

Well, it's actually the Record Source property of the form, not Control Source. And if you are assigning the Record Source in code, then it's a bound form at that point.

Can you try entering a Record Source in the design of the form, just in the meantime, and see if that eliminates that error?

 

[mcalex]

Hi Steve

oops, yeah meant RecordSource.

I'm gonna start again and do it properly. This problem has partly come about because I've tried to make a stand-alone form into a subForm. The way the form started is different to how the subform starts and I think it'll be quicker if I go back to design and use a new subform in place of the existing troublemaker. Consider this solved, and thanks for your help.

 

[SteveSchapel]

Mcalex,

Sounds good. Sometimes starting with a clean slate is the way to go. Best wishes with the rest of the project!