Reusable Form

[macattack]

Unfortunately I'm very new to both Access and VBA so I'm having some trouble getting this setup.

On a form I have multiple buttons. When I push one of those buttons I'd like it to open the new (reuseable) form I've created and have the recordsource be that button's caption (there's a table for each button). Initially I made a form for every table/button but I came across reusable forms and I think that'd be the way to go.

Syntax destroys me since I'm so new to this. I was trying to create variables and having the form's recordsource set with that variable.

Forms!AFT.RecordSource = "LRU"
Where "LRU" is set by which button was pressed. I get the run-time error of: 'LRU' specified on this form or report does not exist.

I have also tried: LRU = "SELECT * FROM AFT "

 

[John Big Booty]

I'd probably look at passing the Table name to the form being called using the OpenArgs portion of the OpenForm method.

You can then test the OpenArgs in the On Load event of the form being called.

Me.RecordSource = OpenArgs

should do the trick.

 

[Galaxiom]

If your reusable form has all the same controls (and their ControlSource) regardless of the RecordSource then it may well be that your data is not well structured.

Are those tables you are using as the various RecordSources all much the same strucure and holding similar data?

If so they should be all in one table with another field to designate the attribute that you are currently using to separate the records into different tables.

Then you would use the WhereCondition to select the records on the basis of that field.

 

[macattack]

DoCmd.OpenForm "AFT", acNormal, , , acFormAdd, , OpenArgs

Does not work.

Even:
DoCmd.OpenForm "AFT" does not work (with or w/o parenthesis).

I have repeatedly tried using DoCmd.OpenForm to open my forms and it never works so I now use the built-in macros with ms access so I have no idea how to begin OpenArgs since I can't even open the form:banghead:

 

[Galaxiom]

Sounds like VBA execution is disabled. Is the database in a Trusted Location?

 

[macattack]

If your reusable form has all the same controls (and their ControlSource) regardless of the RecordSource then it may well be that your data is not well structured.

Are those tables you are using as the various RecordSources all much the same strucure and holding similar data?

If so they should be all in one table with another field to designate the attribute that you are currently using to separate the records into different tables.

Then you would use the WhereCondition to select the records on the basis of that field.

I have 20 tables (each one assosiated with a different thing at work) and each has 7(8 if counting ID) columns and another set of 8 tables with 3 columns each(4 w/ID). Users input a tracked control number to the applicable table via the reusable form. All other necessary data with that control number fills the other 6 columns in each table, so the table must be linked to the resuable form. I must be able to search each table individually and combined (eventually). I have to be able to go back and edit one of the ID's columns at any time (the memo column).

I assumed having multiple tables would be best for speed. 12 users will be simultaneously interacting 24 hours/5 days a week and I thought if they were all in one table it may cause issues. In theory they should never be in the same table because of the way things are setup at my work. These are old PC's too, they run on Windows 2000.

 

[macattack]

Sounds like VBA execution is disabled. Is the database in a Trusted Location?

I started it from scratch on my work computer. I'd assume there to be no issues, how do I check to see if it's disabled and if it is, where can I enable it?

edit: I just converted the entire form from macro to vba, most of the macros held up so I don't think it's that.

 

[Galaxiom]

The twenty tables should all be in one. The perceived need to search all the tables absolutely clinches it. Database files are stuctured for holding a lot of records and serving them up to multiple users from a single table.

The main factor governing speed is the judicious use of indexes on fields that need them. How many records are expected?

The OpenForm issue is mysterious. It may be that htere is a problem with the form that has the buttons.

You really should look at upgrading those computers. Windows 2000 is over two years out of support. This means that no security updates are produced anymore, leaving you potentially vulnerable, especially if you go online.

What version of Access are you using?

 

[John Big Booty]

Just to clarify the use of OpenArgs you cod should be;

DoCmd.OpenForm "AFT", acNormal, , , acFormAdd, , Me.Form.Name

rather than;

DoCmd.OpenForm "AFT", acNormal, , , acFormAdd, , OpenArgs

as you have not assigned any value to OpenArgs, in your code.

 

[macattack]

The twenty tables should all be in one. The perceived need to search all the tables absolutely clinches it. Database files are stuctured for holding a lot of records and serving them up to multiple users from a single table.

The main factor governing speed is the judicious use of indexes on fields that need them. How many records are expected?

The OpenForm issue is mysterious. It may be that htere is a problem with the form that has the buttons.

You really should look at upgrading those computers. Windows 2000 is over two years out of support. This means that no security updates are produced anymore, leaving you potentially vulnerable, especially if you go online.

What version of Access are you using?

I'd estimate over the course of it's life 10,000 or so records will be created and they will be edited 5 days a week, multiple times a day. I've selected that each column be indexed in each table. The computers are incapable of being upgraded (we're not allowed) and they will never have access to the internet, or any other networks for that matter. There is no security concerns in this environment.

I created a blank form, made a button, and put the docmd.openform line of code "on button click" and it works just fine, so it's got to be a setting on that form, any ideas?

 

[Galaxiom]

Check that the On Click Event property is "Event Procedure" and clicking the (...) button on the property takes you to the VBA proecedure.

Otherwise rather than a setting issue the form may be corrupted.

If so then copy all the controls to the clipboard and paste them onto a new form. Then start a new module for the new form and copy all the code for that form to it.

 

[macattack]

Check that the On Click Event property is "Event Procedure" and clicking the (...) button on the property takes you to the VBA proecedure.

Otherwise rather than a setting issue the form may be corrupted.

If so then copy all the controls to the clipboard and paste them onto a new form. Then start a new module for the new form and copy all the code for that form to it.

I coppied all the buttons to the new form and it was still bugged. I ended up deleting all code (to include subs with nothing in them) and then put in DoCmd.OpenForm "AFT" for onClick and it works now. I guess the button became corrupt. Thanks for your help/suggestions!

 

[Galaxiom]

There is nothing quite so bewildering when using a feature for the first time and it has something wrong with it.

 

[macattack]

Got it working now with:

Private Sub CIU_Click()
DoCmd.OpenForm "AFT", acNormal, , , acFormAdd
Forms!AFT.RecordSource = "CIU"
DoCmd.Close acForm, "LRU Choices"

I just have to do that for each button. One table scares me, so for now I'll stick with individual tables, but I thank you for throwing that option out there as it's something to consider.