Create new Column in table from Form

[Ebes1099]

I have no idea where to put this.

I have a table with Codes in it, then there are a few columns that are different category lists. Codes can have different values in each list. Example, I have List A, List B and List C. Code 1 has a value AB12 in List A, F32 in List B and H19 in List C.

So in my table I have a column for the Codes, then for each List. If a user decides there needs to be a new list of values, how do I go about allowing them to enter the new list and proper values. As of now, there is no column in the table for a new list. I'm guessing there is a way on a form to maybe list all the codes with some text boxes where the user can input values then somehow dynamically create a new column in that table and store the user entered data there. Is that possible. Thanks.

 

[bubba100]

New information should be added by ROWS not COLUMNS. Your table is not "normalized" and will caused you much distress.

 

[Ebes1099]

That doesn't really help. There are 600 Codes in this table, I think it would be a pain to have 600 columns and 5 rows.

Can I not do it this way? Looking at the structure of the data it seems logical that this would be the proper way to create the table. Each row is a code, each column is the code's value in the List that column refers to.

I just don't know how to create a new list.

 

[dhookom]

Listen to bubba100. An ideal structure would have

tblNormalized
==================
Code
Category
TheValue

You could get your table normalized by using a union query:

SELECT Code, "A" as Category, ListA as TheValue
FROM tblWithCodes
UNION ALL
SELECT Code, "B", ListB
FROM tblWithCodes
UNION ALL
SELECT Code, "C", ListC
FROM tblWithCodes
UNION ALL
--- etc ---

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

...then there are a few columns that are different category lists...Example, I have List A, List B and List C...

That's the source of your problem, there should not be fields called "List A", "List B", etc. Rather, there should a "Category Lists" table with these values stored in records.

Then you would be able to match up Codes to Categories, as per Duane's "tblNormalized".

It's always a huge red flag if you need to change your database design dynamically during data entry. Trust us, normalizing you database will save you from huge headaches in the futre.

 

[Ebes1099]

So in that case, my new table would have an instance of the code for every list its in? If I had 3 different value lists, I'd have the 3 rows of that code in my table? Would I just have to make my primary key a combination of the Code-List fields?

 

[dhookom]

I generally create an autonumber primary key in all of my tables. This really simplifies code for updating or deleting in code or queries. It takes only one value to identify a unique record. You can/should create a unique index on a combination of Code and List fields.

If you had 5 columns (1 primary key and 4 lists) and 600 records, this would create 2,400 records.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

Alright I got that all set up and all my forms/queries are adjusted to the new set up. Now I'm looking at the task of having the user be able to enter a new List and associated Values for that new list.

Since the codes won't change, I don't want the user to have to type in every code again. I guess I'd like a form that the user can open (probably continuous with all the codes pre populated) then a List Name box for each code (That can probably be entered once and auto filled in for each record) then a text box where the user can input the value associated with that code? I'm not sure how I can do this so it's adding new records for each code since usually you're only adding 1 new record at a time on a form.

 

[dhookom]

You can use an append query to append the code values and List value.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

I understand the append query but I still don't understand where the values are input.

Do I build a form from the table and only have 1 bound field on it, the Code? Then unbound text boxes next to each Code for the user to enter the value? And I suppose 1 other text box for the List type since that will be the same for all of them?

Then how do I tell the append query to grab the data from the unbound text boxes?

 

[dhookom]

How about providing your new table and field names with an description of how they are used. Typically your append query would be based on a new list record and unique codes. You would display the values in a continuous form with only the [valueField] updateable.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

My table has 3 fields, Code, Category and Val. There are right around 600 codes. It's the same 600 codes for each category. They are specific procedure codes that are standardized throughout the industry.

The reason for this table is they are given different values depending on the type of contract they are being performed under. That's what the Category field is, it tells you what Contract goes with that Code and that value. This makes it so each code is in the table "X" number of times (depending on how many contracts are worked out).

So I have a form for each type of contract and it's a continuous form that Lists all the codes and their value. The user can go in and change those if they want. It is based on a query of the Category field and it grabs all the codes and values for the records that have the corresponding category.

But if a new contract is worked out, there are no fields in the table for that category yet. New rows need to be created for EVERY code and then values need to be entered, and a contract name has to be put in the Category field. Since the category will be the same for all of them that will only need to be entered once and copied in the same on Every new record. The "master list" of codes is stored in a separate codes table.

So somehow, I need a form that will show EVERY one of those codes, and have a place for the user to enter a value for this new contract that's been worked out. I'm imagining some sort of continuous form, with the codes being automatically pulled in, and a input box where the user can enter the value. But then how do I get those values all copied into the table?

 

[dhookom]

You still didn't provide actual table and field names. It would help to know the data types also. I expect the contract table has a category field that is unique to the contract. The other table that is critical is the master list which I expect has about 600 records. You would append from the master list and the single value from the category in the contract table.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

I figured out how I can do the last question I had. I now have a different problem since I moved all the data to this TableNormalized you suggested a few posts ago.

I have the normalized table with 3 fields, Code, Category, Value. Each Code has a record for each category. So there's 8 rows for each code with a different category and a corresponding value.

I want to have a form that shows the Code, and it's value in each Category, but I want that value to be editable. I planned on using combo boxes to store the possible values it can be changed to. I have that all worked out, if I need to go into more detail on that I can, but I'm not sure I need to and I'll save the time for now.

How can I get a form. Have 1 page on the form be for each 1 of the 600 codes, and on that page show the 8 different category values it has and allow the user to change them if they want?

 

[dhookom]

Do you need the user to be able to see and edit values for all codes and categories on the same form? Typically you would use a combo or list box on a form to select a category and have a subform to display all the codes and values.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

That is what I ended up doing. I have a main form with the Code. Then a continuous form for each instance of that code in the table. The code-code relationship relates the main form and sub form.

Another question with this issue. I have 1 query that a report is tied to and the report shows the list of codes and their value for any given category. There's a parameter in the query called Category that will filter the results for me. When I call this report from a button on a form, how do I automatically pass the parameter? The code for the call I have now looks like this.

Private Sub B_Rpt_Click()
On Error GoTo Err_B_Rpt_Click

    Dim stDocName As String

    stDocName = "SVC_Rpts"
    DoCmd.OpenReport stDocName, acPreview

Exit_B_Rpt_Click:
    Exit Sub

Err_B_Rpt_Click:
    MsgBox Err.Description
    Resume Exit_B_Rpt_Click
    
End Sub

 

[dhookom]

I would add a combo box to the form to allow the user to select the category. The code would then look like:

Private Sub B_Rpt_Click()
On Error GoTo Err_B_Rpt_Click

    Dim stDocName As String
    Dim strWhere as String
    strWhere = "1=1 "
    If Not IsNull(Me.cboCategory) Then
        [green]'assuming category is a text field[/green]
        strWhere = strWhere & "AND [Category]='" & me.cboCategory & "' "
    End If
    stDocName = "SVC_Rpts"
    DoCmd.OpenReport stDocName, acPreview,,strWhere

Exit_B_Rpt_Click:
    Exit Sub

Err_B_Rpt_Click:
    MsgBox Err.Description
    Resume Exit_B_Rpt_Click
    
End Sub

Duane
Hook'D on Access
MS Access MVP