How to run make table query on an active table used by a form?

[dsatino]

So the same form takes the user input and displays the crosstab data?

[dpatel1682]

You've seen that happen? I'm surprised. I would not expect any updates to be possible on a form bound to a CrossTab query. The query wouldn't be updatable.

NeoPa,

Form is bound to a table which is created by that crosstab query which gets updated in the process with the source table which creates that crosstab table. Having said that, I need re-create that crosstab table since the source table was updated via form.

Relationship defined:

1. TableA(SourceTa ble)
2. QryA(CrosstabQu ery) that gets data from TableA
3. TableB(Crosstab Table) that gets data from QryA
4. FormA that has TableB as source
5. Edits to FormA updates TableA - This will require me to re run the QryA which creates TableB

Hope I made myself clear and Thank you all very much for your help

[dpatel1682]

So the same form takes the user input and displays the crosstab data?

Yes, same form takes the user input and displays the crosstab data.

Here's the steps defined to make this more clear

Relationship defined:

1. TableA(SourceTa ble)
2. QryA(CrosstabQu ery) that gets data from TableA
3. TableB(Crosstab Table) that gets data from QryA
4. FormA that has TableB as source
5. Edits to FormA updates TableA - This will require me to re run the QryA which creates TableB


Thank you all very much for your help

[dsatino]

Is there any way you can zip the file and attach it?

[dpatel1682]

I am sorry I can't send the file. Is there any other way that I can explain myself better

Thanks a lot for your help

[dpatel1682]

Is there any way you can zip the file and attach it?

I am sorry I can't send the file. Is there any other way that I can explain myself better

Thanks a lot for your help

[dsatino]

How about a screen shot of 'FormA' that might help me see the gist of what you're doing

[NeoPa]

dpatel1682:
4. FormA that has TableB as source
5. Edits to FormA updates TableA - This will require me to re run the QryA which creates TableB

How can step 5 talk about updating TableA when step 4 indicates FormA is bound to TableB?

[dpatel1682]

How can step 5 talk about updating TableA when step 4 indicates FormA is bound to TableB?

NeoPa,

I am running vba afterupdate event when FormA gets updated, based on the account # it updates TableA.

Hope this makes sense.

Thanks

[dpatel1682]

How about a screen shot of 'FormA' that might help me see the gist of what you're doing

FormA is a datasheet which filters the databased on the user selection from the listboxes above

[dpatel1682]

FormA is a datasheet which filters the databased on the user selection from the listboxes above

Screenshot attached in the main post

[dpatel1682]

How about a screen shot of 'FormA' that might help me see the gist of what you're doing

Here you go

[dsatino]

Is this a form with a subform?

[dsatino]

I'm guessing at what's going behind this form which I think is either:
a main form bound to tableA and subform bound to tableB
or
the form is bound to tableB and edits via the form are in unbound controls that update tableA

Either way, constantly remaking the table is not good form. I can't remember the last time I bound a form to a crosstab, but I think the following is generally what you need to do:

The form can be bound to the crosstab query in the same manner that it can be bound to the table. Instead of remaking a table you can simply bind your form to the query and use the .Requery method. For a crosstab, the best way to do this is to go into the query properties and set the column names, but since you're pivoting on dates I'd assume they are variable. In this case you'll need some VBA in which you open a recordset based on the crosstab and use the field index to name the columns before you requery.


There may be some rust in there, but hopefully it will point you in the right direction. As NeoPa first stated, you don't want to remove the forms recordsource.

[dpatel1682]

Subform/Datasheet is bound to tableB (which is created by a crosstab query) and TableA is being updated using VBA sqlstring. Is there any way I can close the form once I close it, I recreate the table? I tried doing that using docmd.close and docmd.open method but when I try to run the macro which recreates the TableB, it gives me the error mentioned before. Any way to clear the MS Access memoro / unlock that table?

Thanks