Textbox Control in Form

[anshaik]

I feel like this should be really simple, but for some reason have not been able to figure out how to do this or where to look:

(Using Access 2003, attempting to code a form)

I have one table from where I will be pulling my information, and a set of columns I am interested in. I have a form in which I have 2 sets of textboxes, the first set is to be a "Label," or "Heading" (Not sure of the exact terminology in Access), which will be a column my table. The user will type in the column heading into this textbox. After typing the heading into the textbox, the user will click a "Update" button, which is supposed to save that string into the textbox.

Now, how do I code into the update button that will allow that string to be saved in to the textbox for future viewing? Also, once it is saved, what method will I utilize to get the saved string from the textbox? The form automatically saves into the instance of the form that string, and is shown in all the other records. When I close the form, it is cleared.

I will explain the second set of textboxes as well, though I have not attempted this yet: once the first textbox has a saved string value of the header in the table, the second set of textboxes will pull the information to it from the table I have. I think this part would not be as difficult once I figure out my first question, but any suggesions are appreciated.

Thanks for any help

 

[jzwp22]

The user will type in the column heading into this textbox. After typing the heading into the textbox, the user will click a "Update" button, which is supposed to save that string into the textbox.

The textboxes are designed to save information into a table not into the textbox itself. If you are using unbound textboxes (not tied to a table) then when you close the form, whatever was typed in the textboxes is not saved anywhere. If you want to have Access save what is typed into an unbound textbox, then you must store the information in a table. When the user clicks the "Update" button you will need an append query to run that appends the textbox values as a new record in a table. You will also have to capture the user information, so that the information is tied to that user.

Now, once you have captured the text the user put in, how would it relate to the field names (what you call column names) in your table?

Wouldn't it be better to have the user select the header or label from your list of field names?

 

[anshaik]

I am using unbound text boxes, my goal is to leave one string in that text box, which would ultimately be a header for my table.

By what I understood from what you said is that this is not possible, unless I store the information in a new table? So, the there is no function for the text box itself to hold any information? I was hoping that with the "Update" button the text box would use some method to update the string value within itself. Or can I make this in to a label, and have the user have the ability to edit the label?

The reason I am doing this is because as of now, I have six headers in my table, that have corresponding information in them (in the table of interest). However, I want the ability to have the user add or remove the information being shown, possibly up to 10 new columns. Therefore, my thought was, I would leave the table with these six columns intact, and have the user have the ability to enter in to the set of text boxes the information they need shown. I have other functionality in which the user can add columns to the table and add information to it.

So, basically the set up is like this (if my previous description is too convoluted now):

20 blank textboxes (which I want the ability for user to enter the "headers") -- where I am having the issue

parallel to another 20 text boxes that refer to the previous ones to find which column they need to look at.

As for what is actually store in the table, they are locations (the headers are), therefore, the users know which location they want to look in to.

 

[jzwp22]

By what I understood from what you said is that this is not possible, unless I store the information in a new table? So, the there is no function for the text box itself to hold any information?

No

Or can I make this in to a label, and have the user have the ability to edit the label?

If you make it a label, it becomes part of the form design itself. If you want it to be different for each user then you will need a separate form for each user which is not the way to go. There is a way, but it to is not very efficient. You can store the text in a table and then alter the form design when it is opened, but you will be editing the form each time it is opened. Of course, once the form is opened in design view and the label text changed, the form will have to be resaved before the user can input their other data.

The reason I am doing this is because as of now, I have six headers in my table, that have corresponding information in them (in the table of interest). However, I want the ability to have the user add or remove the information being shown, possibly up to 10 new columns. Therefore, my thought was, I would leave the table with these six columns intact, and have the user have the ability to enter in to the set of text boxes the information they need shown. I have other functionality in which the user can add columns to the table and add information to it.

This whole scenario you describe concerns me. A user should not be altering the table design. A user should just be entering data into a preset design. Usually forms are set up so that the user just enters data which then gets put into the appropriate fields within a table. Having an increasing number of fields in a table is also a sign that your database will end up not being normalized. You many want to take a look at this site for more on what normalization is and how important it is to setting up a relational database.

Can you explain more about what this database is going to be used for and what type of information is to be stored?

 

[anshaik]

The database is a list of telephone numbers and corresponding information for those numbers, the columns I am right now referencing are locations that the set of numbers are associated with.

As of now, there are only six locations each number can be associated with. The reasoning for doing what I was talking about was because the people I am creating this for wanted a way in the future to add more locations of perhaps to delete existing locations. Of course, this probably won't happen very frequently--it is just a possibility. I first created the form with just those six locations as labels and coinciding information alongside it bound to the table in question. However, they were concerned that they might need to add/remove locations, leading me down this path of having a user enter their own information set (columns).

What do you suggest I do?

 

[jzwp22]

First, the locations should not be field names. They must be records in a table. You can add any number of records to a table as you want.


Before doing the forms, you have to have the correct table structure. You say that a phone number can be associated with many locations. Can a location be associated with more than one phone number? (You have to ask question like this in order to develop the correct table structure).

 

[anshaik]

Not exactly--I didn't elaborate well enough, it's not exactly a "location," it's more of a..pointer, I guess? I'm not sure what it'll be called, but a routing command for that specific location on that number that is being called on. Therefore, each "location" has a series of records associated with it, for each number (which is the key). For example, number 1 might have 5 locations, which are fixed, and each has a command number associated with it. The 6th location is null.

 

[jzwp22]

No matter what you call it, you still have a one(number)-to-many(pointer) relationship which is handled with two tables as follows:


tblNumbers
-pkNumberID primary key field, autonumber
-yourNumberfield (you'll have to select the appropriate data type based on your data)


tblNumberPointers
-pkNumberPointerID primary key field, autonumber
-fkNumberID foreign key to tblNumbers (this forms the relationship with tblNumbers) this must be a long number datatype field.
-fieldforthepointerinformation

For your forms, you would have a main form based on tblNumbers and on that for you would have a subform based on tblNumberPointers. The textbox controls would be bound to the respective fields in the underlying tables. Your users would enter data via these forms. There is no need for the unbound controls on the form and there is no special updating needed since the forms would be bound to their respective tables.

 

[anshaik]

Ok, I will try to implement this tomorrow morning. Thank you for the feedback, I'll get back to you with more questions once I think through this and try to solve my problem with this method.

 

[jzwp22]

Sounds good. Let me know if you have additional questions.