Interesting issue regarding setting a forms RecordSource

[grantwilliams]

Hi All,

I have an unbound subform which contains fields bound to a query which is assigned programatically using the following code:

Forms!Main!MainSub.Form.RecordSource = "qrFrmSubSetup"

The reason I have chosen to do it this way is that I have a temporary database which remembers the Event selected. When the subform is opened, it opens the temp DB as a recordset and assigns the value remembered to the first field. I then assign the recordsource (query) to the subform which fills the subsequent fields.

The query selects the record from the Event table which has the ID selected in the first field.

When I update the first field, both the query and the temp DB respond to the new value selected and the subform will set the remainder of the fields to null UNLESS I have selected the first record of the Event table. This is also the case when opening the subform i.e. unless I have the first record of the Event table selected, I end up with a whole lot of empty fields.

Anybody seen this before?

 

[TheAceMan1]

How are ya grantwilliams . . . . .

Either my communication skills are failing me or I'm just not making sense of this.

More than anything else, I have to ask:

[blue]What is the purpose of all this?[/blue]

See Ya! . . . . . .

 

[grantwilliams]

Hi TheAceMan1,

Sorry about that, I was very rushed this morning when I put this up.

I'm putting a system together for the management of sporting events held by an association. To start with, I'm using an Access database to develop a prototype before getting approval to continue by the association.

Some background

I have:

1) a table, tEvent
2) a query, qrFrmSubSetup
3) a form, Main
4) a subform of the form Main, SubSetup (not bound to any table or query)
5) a temporary database, with a table wEventID. I use this for remembering the EventID as a number of tables record this ID.

When I open the form, Main, and then the Subform, SubSetup, the subform uses a recordset to read the table wEventID in the temp DB and assign the value in this table as the default value for a combo box, Competition, on the subform. This combo box is unbound and has a rowsource using fields of the tEvent table. When the combo box is updated, in turn, the table wEventID is updated, replacing the previous value.

The other half of this equation is that the remainder of the fields in the subform are bound to the query qrFrmSubSetup. After the combo box value is assigned (or updated), the database calls for this inding to occur (through the line of code provided earlier). The remainder of the fields SHOULD be populated following this command.

The issue

IF the combo box, Competition, has the first record of the table tEvent selected (through being assigned by the wEventID table or being updated on the subform), then the remainder of the fields populate as expected.

IF the combo box, Competition, does not have the first record of the table tEvent selected, then the remainder of the fields are not populated. In this instance, the wEventID table is updated with the appropriate record AND the query, qrFrmSubSetup, has the appropriate data, but this data is NOT fed into the subform.

I have no idea why!

Can anyone provide ANY info? I hope I'm making this a little clearer!!!!!

 

[MichaelRed]

Shakesperian?

For any form, setting the recordsource is sufficient, procided the recordsource object include the necessary and sufficient. In this instance, that would seen to include the where clause with the record ID as a parameter. It the query includes the combobox as the source for the where term all should follow. If the combo box is a BOUND field to the query nonsense will follow. If you understand the process / description clarity may follow.




MichaelRed

 

[TheAceMan1]

grantwilliams . . . . .

Where to start? . . . . hmmmmm . . .

[blue]5) a [purple]temporary database[/purple] . . . I use this for remembering the EventID as a number of tables record this ID.[/blue]

You need a [purple]temporary database[/purple]!
[ol][li]These tables can be [blue]local and independent[/blue] in your Main DB.[/li]
[li]If you insist on this [purple]temporary database[/purple], make it permanent and [blue]link the tables[/blue] to the Main DB. [blue]You'll be able to use the tables as if they were local.[/blue][/li]
[li]Both the above are [blue]easily cleared[/blue] with [blue]Delete Query[/blue] and since the tables now have local access, all you need for the [blue]record source[/blue] of your combobox is the table name, or the name of a Query that returns your desired rcordset.[/li][/ol]
As for the rest . . . . there's too much to explain:
[ol][li]Tables are apparently improperly setup.[/li]
[li]Your combobox operations would'nt be necessary if the above were setup proper.[/li]
[li]Your updating of tables is totally inconsistent with Relational Database operations.[/li][/ol]
Forgetting you have a DB, [purple]perhaps if you explain (back when you were brainstorming) what your doing and the results your after![/purple]

I don't know if your pressed for time, but at some point do read the following ([blue]the hub of relational Database Design[/blue]). If you get thru the links with understanding, a new door should open, as well as your eyes:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

Understand . . . as you've posted, tipsters will be reluctant. Too much is out of context with DB operations.

Your thoughts . . . . .

See Ya! . . . . . .

 

[grantwilliams]

TheAceMan1,

Sorry, but I think I've overcomplicated this by trying to explain it to you further.

My issue is simply that I have an unbound combo box (which uses data from the tEvent table) and when I select a record in this combo box, I'd like the remainder of the fields to be updated. The remaining fields are bound to the query qrFrmSubSetup. When I update the combo box, one of two things occurs:

1) I select the first record in the combo box. The fields update as expected.
2) I select any record EXCEPT the first record in the combo box. The fields do not update, however the query DOES have the appropriate data, it's just not feeding through.

I thought it may be that the query (or the form) is not refreshing with the new data because it may have been locked, however, if I close the form with any record other than the first selected, the database remembers this (using the "temporary" mdb) when I reopen the form and if I then change the combo box back to the first record, the fields are updated, so it appears the form/query IS refreshing when the new record is selected in the combo box.

I simply don't understand why it all works for the first record and then doesn't for ANY of the subsequent records!

I don't have access to the DB at the moment, but can post code/queries etc if you'd like to see how I'm attempting to do this.

As for my "temporary" database, it IS in fact permanent AND linked to the main database. Any and all references made to tables within this database are made programatically so that any errors e.g. the mdb file does not exist, the table doesn't exist etc are caught. What is "temporary" about this database is the data stored in the tables i.e. I have used these tables in the past to remember custom settings (e.g. who the user is etc) and for collecting customised report data (rather than using a query on a query, I use a query which drops the required record ID's into one of these tables and then use a second query on this table to pull out the data for these records that I'd like to report. Anyways, my issue is NOT anything to do with the "temporary" database, it is with feeding the record queried into the subform.

 

[grantwilliams]

I've found the problem now, just not sure how to fix it. I made some changes so I could open the form in its own right (rather than as a sub-form) and it works fine. For some reason, if I open it as a sub-form, it's giving me the problems above.

 

[TheAceMan1]

grantwilliams . . . . .

Sorry to get back so late (Mom not well).

Getting back to when it was a subform . . .

For the [purple]combobox[/purple] post back the following:
[ol][blue][li]Column Count[/li]
[li]Column Widths[/li]
[li]Row Source Type[/li]
[li]Row Source:
[ol a][li]If [purple]TableName[/purple], list the fields as they appear from left to right (including any fields with width set to zero in Column Widths).[/li]
[li]If [purple]QueryName[/purple], post the SQL.[/li]
[li]If [purple]SQL[/purple], post it.[/li][/ol][/li]
[li]Bound Column[/li]
[li]Post the code in the event your using for when a selection is made.[/li][/blue][/ol]
Other required Info to post back:
[ol][li]The SQL of [purple]qryFrmSubSetup[/purple].[/li]
[li]Was the subform linked to the mainform with [purple]Link Master/Child[/purple] properties?[/li][/ol]
Please post back all. I know its a bitbut it will explain alot thats hidden . . . . .

[purple]I await! . . .[/purple]

See Ya! . . . . . .