Opret Query med subdatasheet

"Jeg kan godt oprette en query" In code, or in query designer?
If you can do this in code then you can also change the parameters by altering the underlaying SQL.

To alter parameters for a form youmust open the form in design view which can also be done in code.

You dont need to open the form in design view to change the recordsource.

Me.recordsource = "query1"

"...samt omdanne et givet felt i denne til en combobox"
This would require opening it in design view, but another idea would be to have two fields, one a combo and another a plain text field. Then depending on what you wan to use you can hide one or even both of them.
Maybe an expanation of what you are tryin gto do may help.

I can create a query in code and I can create the recordsource and assign it.

There is no form involved with this. Just one (let's call it Query1) with a subdatasheet assigned (Query2). Like with a subform i can set the child and masterproperties in qeury properties.

My DB works as a frontend for an SQL-server. Each time i refresh the tablelinks, the properties for each table and associated queries are reset. This means I would have to make the combo's, set the subdatasheet properties etc. each time I make a refresh.

So I would like to delete (just to make sure) and create the queries in question each time the user opens the DB. This involves setting the subdatasheets properties.

which properties are reset?

"This means I would have to make the combo's, set the subdatasheet properties etc. each time I make a refresh."
which ones are we talking about?

try and give some examples

"Like with a subform i can set the child and masterproperties in qeury properties." agai which ones are you refering to?

- "which properties are reset?"
For instance: You can change a field to a combobox in table design even when it is connectecd via ODBC. All the trouble of setting the rowsource, setting columcount etc. you will have to do all over if you refresh the link.

- "Like with a subform i can set the child and masterproperties in qeury properties." agai which ones are you refering to?

I am referring to....the child and master properties! When you insert a subdatasheet you need to link the masterquery and the childquery (most commonly with an ID-field). This is done in Query properties for the masterquery, just as it is with forms and subforms.

I'm soory I dont know much at all about you rprogram so its difficult for my to understand your problem.

Why do you need to refresh the linked tables? Are you changing which database you are using?

When you add a sub form to a main form they are normally linked through fields in the underlaying table/query through properties in the sub form (Link child fields + link master fields).

Refreshing the linked tables to your SQL server should have NO effect on these properties, unless of course you are also altering the fields which are available on the form(s)

When you say "This is done in Query properties for the masterquery, just as it is with forms and subforms."
do you mean in query design where you JOIN on primary and foreign keys?

I need to refresh if I change a fieldtype or add a field to the table.

I am aware of the linking of child- and masterfields in a form. This is however not a form, this is a query. The reference to forms is only for explanatory purposes.

"do you mean in query design where you JOIN on primary and foreign keys?":
No, this is not related to the recordsource of the query. It

Please make a query, open in designmode, choose properties of the query (like with a form) and you will see a field called "Subdatasheet Name", and just below "Link Child Fields" and "Link Master Fields".
This is what I'm talking about :-) I want to set these programmatically.

I made query called Query1 and ran the following code:

Set qdef = CurrentDb.QueryDefs("Query1")
On Error Resume Next
For n = 1 To qdef.Properties.Count
    Debug.Print qdef.Properties(n).Name & ": " & qdef.Properties(n).Type & " - " & qdef.Properties(n).Value
Next n

This is the result:

DateCreated: 8 - 25-09-2006 14:06:48
LastUpdated: 8 - 25-09-2006 14:43:24
Type: 3 - 0
SQL: 12 - SELECT tblHenvendelse.fldHenvendelseID, tblHenvendelse.fldWeekID, tblHenvendelse.fldHenvendelseFormID, tblHenvendelse.fldHenvendelseBackGroundID, tblHenvendelse.fldAntal, tblHenvendelse.fldStudievalgID
FROM tblHenvendelse;

Updatable: 1 - True
Connect: 12 -
ReturnsRecords: 1 - True
ODBCTimeout: 3 - 60
RecordsAffected: 4 - 0
MaxRecords: 4 - 0
RecordLocks: 2 - 0
RecordsetType: 2 - 0
OrderByOn: 1 - False
Orientation: 2 - 0
SubdatasheetName: 10 - Table.tblHenvendelseSpecial
LinkChildFields: 10 - fldHenvendelseID
LinkMasterFields: 10 - fldHenvendelseID
DefaultView: 2 - 2
GUID: 9 - ????????
DOL: 11 - ??  ????????  ????    tblHenvendelse  ???????> ????????fldHenvendelseID  ???????? ????????fldWeekID  ???????? ????????fldHenvendelseFormID  ???????? ????????fldHenvendelseBackGroundID  ???????? ????????fldAntal  ???????? ????????fldStudievalgID  ????????  ???????tblHenvendelseSpecial  ???????? ????????fldHenvendelseID                  
Description: 10 - Testtest
SubdatasheetHeight: 3 - 0
SubdatasheetExpanded: 1 - True
NameMap: 11 - ??  ????????  ????    dbo_tblHenvendelse  ???????? ????????fldHenvendelseID  ???????? ????????fldWeekID  ???????? ????????fldHenvendelseFormID  ???????? ????????fldHenvendelseBackGroundID  ??;????? ????????fldAntal  ???????? ????????fldStudievalgID  ???????? ????????fldTimestamp                  

I just can't set the subdataSheetName

I just found the solution:

Set qdef = CurrentDb.QueryDefs("Query1")
qdef.Properties("SubdatasheetName").Value = "Table.NewNameOfSubTable"

No need to open in designmode.

Svar

Great :o)

But lets say you do add new fields or change the type etc. To change the query parameters in code requires you to change the code to be able to reflect the changes. So you might as well make the changes directly to the query parameters. Or you would have to find another method to make the changes dynamically.