New to learning how to pass values to a routine ...

[sjs94704]

Hi, All.

I am just beginning to get into the area of passing variables as needed. I have a good grasp of the concepts, it is just the code to make it happen that is where I am just getting going.

For this I have several pictures so you can see exactly what I have set up and I will do my best to explain as I go.

As we all know, in databases we use normalization to help make things work better. So, I have accumulated multiple tables and each are 'codes' tat I use in different part of my DB. However, the table structure is exactly the same for each table, so there is no point in building a separate form for each code!

GOAL:

When a command button is clicked on a form it is assigned to open this popup form and display a specific list of codes.

1. Before the form opens the labels of the form are set
2. Then the Recordsource and controls properties are set
3. The form opens and is populated with the list of all of the codes from the table corresponding to the button pressed.

Likewise, when the user is DONE, there should be a routine that dose the reverse and deletes all the information it just put in about the code they were just working on to keep any confusion from happening to another code table.

I know this is probably pretty easy, I just have not done it very much yet meaning using the same form over and over.
For me it is just about building the skill of creating those routines that handle this stuff.

So, allow me to show you a few pictures of what I have so far.

Here is a picture of the form itself. It is set up as a POPUP form.
In the example below it is showing a list of ADDRESS TYPES.

In regards to the code status, here is the business rule:

Let's say at some point we stop using PO Boxes as mailing addresses.
BUT, we have 5,000 customers who have a PO Box on their records.

We are not going to tell them they have to stop using them, we just don't let NEW
customers use them!

So, on this screen the person maintaining the database can pull up the address
types and just change PO BOX from ACTIVE to INACTIVE.

The combo boxes throughout the DB are dependent on a QRY that only displays ACTIVE
codes!

Form Name: PFrmCodeEditF

See text field labels names in header

Text Control: txtCodeName
Combo Box Control: cboCodeStatus

Here is the Address Type Query. Someone correct me if I am wrong, but I have no use for the PRIMARY KEY
field on my form. Once I saved and reopened this the primary key field was gone. The only two field I care about are
such as in this case AddressType and StatusID.

Just for the hell of it, here is another code I use so you can see that the structure is the same

For the StatusID fields here is a list of the choices:

 

[ebs17]

However, the table structure is exactly the same for each table

The same data belongs in the same table and in the same fields.

 

[Edgar_]

It sounds like you are violating some normal form, you should consider consolidating these similarly structured tables into a single table with an additional column that distinguishes the records or using relationships between tables to link related data rather than duplicating the schema and modifying the recordsource of the form.

 

[sjs94704]

It sounds like you are violating some normal form, you should consider consolidating these similarly structured tables into a single table with an additional column that distinguishes the records or using relationships between tables to link related data rather than duplicating the schema and modifying the recordsource of the form.

OK, I can do that (Adding another distinguishing field) So, I am just going to list off a few that I used from a previous database:

Customer Type
Phone Type
Gender
Marital Status
Verteran Status
Vendor Type
Part Type
Part Category

I have always had these in their own seperate tables. Maybe there is some simple way of including all of this into one table?

 

[Mike Krailo]

After reading through post one, I fail to see a specific question regarding the title of the thread. Pass values to a routine. Where is the routine you speak of? And what values are we talking about?

 

[Edgar_]

I wrongly stopped reading your opening post after the part where you're reusing table schemas. Those tables are not similar. You should not have one form to handle all those types of information. Your form will become very hard to maintain.

By the way, I don't see where you're passing variables.

 

[Josef P.]

I can understand the desire for only one form. I see the maintenance of the data in this table (#4) as an "admin task" and not for every user.

Implementation via a "maintenance form" I would design like this:

• Unbound main form with a listbox or combo box to select the desired table.
• Subform for the processing of data

When passing the table name to the subform, I would set the data source accordingly.

Example:

Select Code_AddressTypeID as ID, Code_AddressTypeStatusID as StatusID, Code_AddressType from Code_AddressTypeT
Select Code_ContactTypeID as ID, Code_ContactTypeStatusID as StatusID, Code_ContactType from Code_ContactTypeT
...

This allows you to use the subform bound in the normal way. The data source (stored query or SQL text in DataSource) controls the access to the desired table.

It may even be possible to do something like this:

public sub SetDataSource(byval TableCode as string)

    const TemplateSql as string = "Select Code_{TabCode}TypeID as ID, Code{TabCode}TypeStatusID as StatusID, Code_{TabCode}Type from Code_{TabCode}TypeT"
    dim SelectSql as String

    SelectSql = replace(TemplateSql, "{TabCode}", TableCode)
    me.RecordSource = SelectSql

end sub

Call from main form:

me.SubFormControl.Form.SetDataSource "Address"

"Address" is a value from Listbox/Combobox.

 

[sjs94704]

I can understand the desire for only one form. I see the maintenance of the data in this table (#4) as an "admin task" and not for every user.

Implementation via a "maintenance form" I would design like this:

• Unbound main form with a listbox or combo box to select the desired table.
• Subform for the processing of data

When passing the table name to the subform, I would set the data source accordingly.

Example:

Select Code_AddressTypeID as ID, Code_AddressTypeStatusID as StatusID, Code_AddressType from Code_AddressTypeT
Select Code_ContactTypeID as ID, Code_ContactTypeStatusID as StatusID, Code_ContactType from Code_ContactTypeT
...

This allows you to use the subform bound in the normal way. The data source (stored query or SQL text in DataSource) controls the access to the desired table.

It may even be possible to do something like this:

public sub SetDataSource(byval TableCode as string)

    const TemplateSql as string = "Select Code_{TabCode}TypeID as ID, Code{TabCode}TypeStatusID as StatusID, Code_{TabCode}Type from Code_{TabCode}TypeT"
    dim SelectSql as String

    SelectSql = replace(TemplateSql, "{TabCode}", TableCode)
    me.RecordSource = SelectSql

end sub

Call from main form:

me.SubFormControl.Form.SetDataSource "Address"

"Address" is a value from Listbox/Combobox.

That's what I meant about passing variables! I did not know how to do that and was wanting to figure out how! I know it will take practice, BUT, this is what I meant!

Thanks, Josef!

And I do appreciate everyone else's responses in trying to help figure out what I was talking about. From my point of view I was not sure what else I could say... I will work harder in future when asking questions to try to be more clear..

 

[Pat Hartman]

I solved this problem approximately 40!!! years ago using COBOL and DB2 (IBM's RDBMS) It got installed into multiple applications and BE versions over the years and eventually got translated into Access. Now it is two tables, two forms, two reports and the tables are whatever you use for a BE. So, for some apps, they are ACE, for others SQL Server, for others they might be DB2, Oracle, Sybase, etc. Whatever the client wants the BE to be. The attached PPT file might be slightly out of date.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk