Question Change the Control Source of a Form

[gunny3000]

Does anyone know hoe to change the Control Source of a Form from a ComboBox within the Form itself?
For example, I have several Queries with data in. Each Query has a unique name (obviously). The selection from a ComboBox generates one of the Query names. This is confirmed to me in Design Veiw when I have a TextBox displaying the recent selection from the ComboBox.
I have tried a simple Combo_Click, and, the same code for Combo_AfterUpdate:
Private Sub Combo13_Click()
Form.RecordSource = Combo13.Value
Form.Requery
End Sub

And, I have also tried registering the value of the ComboBox as a string:
Private Sub Combo13_Click()
Dim ag_Record_Source As String
Combo13.Value = ag_Record_Source
Form.RecordSource = ag_Record_Source
Form.Requery
End Sub

 

[DCrake]

Why are you wanting to change the recordsource. Surely the contents of each query will need the same fields as the form otherwise you will get errors. If the queries are returning different records from the same table thn you need to look at filtering the queries instead.

 

[gunny3000]

Hi, thanks for your reply. I would like to evoke diffirent Queries because they have unique number references. therefore, when i choose to add a new item with the unique reference number i can easily do so and keep in a uniformed manner.

 

[DCrake]

Agreed, but do all the top level queries have the same underlying table(s)?

 

[gunny3000]

Yes, absolutely correct. All the tables/queries will have the same structure and similar data. The underlying difference is their unique reference number because they are for descretly different physical processes.

WHat are you thinking?

 

[DCrake]

For a start is the form used for adding records is it being used for editing also.
Also how are you determining which record source to use? I know you referred to combo boxes.

Normally I would use the after update event of the combo

Me.RecordSource = me.combo

We are talking about the main form and not subform record sources here? arn't we?

 

[gunny3000]

I am using one form to view and edit the data. and therefore, I would like to select PC or TC or Ex for Potato Chip, Tortilla Chip or Extusion processes. So very simply, with a blank form open, I would select from a Combo either PC, TC or Ex which would then call-up the relevant Query. I could then search through existing entries (editing or reading), or, add a new one. but, conversly, I could go back to the Combo and change to TC for example from PC and do the same crusing through the data.

Therefore, I would need the Form to Requery on the new Query Table.

Is that clear?

 

[DCrake]

Can you post a copy of your mdb?

 

[gunny3000]

How can I attach a Db?

Also, the ChangeManagement Database is very primitive at the minute. I'm just trying to conquor these basics for the overall operation. Once I have them cracked then I'd be in a position to create the full ChangeManagement Database.

 

[DCrake]

Use the paperclip icon to attach the mdb. Run a compact and repair first though

 

[gunny3000]

OK - many thanks.

The Form I am trying things is 'Form1'

See what you can think of.

 

[DCrake]

You are making a rod for your own back going down this route. If you want to maintain a unique number for each process type with a different prefix you can do this all in 1 table. And do you really need to make the primary keys 1,2,3,4 for each type. Users will not se the PK so it does not matter.

Ok have an autonumber field but then have another field for the Primary key

FldPK

This consists of the prefix and 0000 suffix. Every time you want to ad a new record get the app to get the next defaut ID based on the selection

If you add a new type of category later on you will need to do alot more development to include this new category. You table shoud look like this

FldId___FlkPKey
1 PC0001
2 PC0002
3 PC0003
4 TC0001
5 TC0002
6 TC0003
etc

 

[gunny3000]

OK, I'll have a re-think.

Thanks for all your help.

 

[gunny3000]

Actually, If I didn't do it the way I have started, how would I be able to scroll through the existing entries?

And, How could I add a new entry on one of the processes?

 

[gunny3000]

Hi DC, How can I create 1 Table with the possibility of numerous 0001's with a different prefix? i.e. PC0001, TC0001, Ex0001, WW0001, etc, etc.

Actually, If I didn't do it the way I have started, how would I be able to scroll through the existing entries?

And, How could I add a new entry on one of the processes?

 

[gunny3000]

Hi DC,

I didn't understand your last post.

PLEASE HELP - I'M DEPERATE.

Could you forward me a basic DB?

 

[DCrake]

The question is why do you need individual repeating numbers for the records?

All you really need is a primary key to uniquely identify the record and a category code to enable you to isolate different record types.

Then in your for if you select PC then you then call the one query here category = PC. Is there some reason for having grouped numbers. Also what happens if you reach 9999+

I have read your PM and if you want my help on a professional basis request my help though my website.

 

[gunny3000]

David,

you know, as much as I hate to admit it and concede defeat, I'm going to just work on the basic autonumber generation; and therefore, acheive results like PC0001, TC0002, PC0003, WW0004, etc, etc. I would still be able to filter on the prefix and produce reports, etc.

To answer your question 'why?' - I was keen to stay as close as possible to a "paper and pen" method that was originally adopted. Therefore, sequencial numbering was the name of the game.

thastnks for your support and advice.

 

[DCrake]

There is nothing wrong in having a Psuedo Primary key in another field that conforms to your theory. Every Time you add a new record yu simply do a count on all records = PC then add one to it and store the number there. You can use this field if you have data to migrate later.