User Profile

dpatel1682 · Aug 5 '10, 08:57 PM

It worked. I used the following Code

Code:

tbl_Raw_Data_by_Account_subform.Form.RecordSource = ""

DoCmd.RunMacro "mcr_mktbl_Raw_Data_by_Account"

tbl_Raw_Data_by_Account_subform.Form.RecordSource = "tbl_Raw_Data_by_Account"

tbl_Raw_Data_by_Account_subform.Form.Refresh

tbl_Raw_Data_by_Account_subform.Form.Refresh

dpatel1682 · Aug 5 '10, 08:02 PM

ok I tried that but it didn't work. I created a module with a function called mktbl which had maketable qry followed by open form command. It gave me the same error when it tries to execute maketable qry

Any other idea?

Thanks for your help

dpatel1682 · Aug 5 '10, 06:35 PM

Can you please provide an example? I am still bit lost as to how do I do this. What I tried to do after the update is click on the "Refresh" button which will close the form, run the make table qry, and re open the form. Should I put the above steps in module and run that module on click ?

dpatel1682 · Aug 5 '10, 05:37 PM

I am kind of unsure in what do you mean by "Start by getting your update procedure out of Form and into it's own module. Once you do that, find a way to call the process without the form being open and it'll probably work"

Thanks a lot for your help

dpatel1682 · Aug 5 '10, 04:26 PM

Govnah,

What i did was I wrote a code in the after update event which sets a record set for the next list box. If you have multiple list boxes which needs to get refreshed based on the first input then you have to write code for the other boxes based on its value. Here's the sample

Code:

Private Sub List5_AfterUpdate()

Dim sAccountName As String
Dim sSubAccountType As String

    sSubAccountType

...

dpatel1682 · Aug 5 '10, 03:50 PM

I agree with you comments regarding doing everything against the standard ways. I had PK and referentials before I received the data and when I looked at the data, I was forced to kill all the PK and Referentials. After reviewing the DB, is there any way to achive what I am trying to do on the same form?

Please advise

Thanks

dpatel1682 · Aug 3 '10, 09:06 PM

last subform with 2 lines

dpatel1682 · Aug 3 '10, 08:34 PM

Here you go. Thank you so much for your help

dpatel1682 · Aug 3 '10, 07:30 PM

Here's the db that I have with truncated data. Please let me know if I can explain it to you futher

dpatel1682 · Aug 3 '10, 04:16 PM

My table is really small ~4000 records and I am updating everytime user click refresh button. Any other way to do this by staying in the same form? I know I can have another form pop up for the update and once it gets updated I can display the result in the 1st form via query as a source of that datasheet. But I do want to avoid going into another form for the updates

dpatel1682 · Aug 3 '10, 03:37 PM

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 a lot for...

dpatel1682 · Aug 3 '10, 03:36 PM

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 a lot for...

dpatel1682 · Aug 3 '10, 03:36 PM

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

dpatel1682 · Aug 2 '10, 09:30 PM

Here you go...

dpatel1682 · Aug 2 '10, 09:26 PM

Screenshot attached in the main post...

dpatel1682 · Aug 2 '10, 08:34 PM

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

dpatel1682 · Aug 2 '10, 08:26 PM

NeoPa,

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

Hope this makes sense.

Thanks...

dpatel1682 · Aug 2 '10, 07:57 PM

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 · Aug 2 '10, 07:18 PM

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 · Aug 2 '10, 07:00 PM

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...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment: