Making table

[The Bey]

Can I use a query to make a table that will display values from text boxes on a form?

I understand how to make the table so that each value will be displayed in a seperate column, but can I get 4 different text boxes to be displayed in different rows (in the same column)?

 

[jzwp22]

You would need some code to loop through the controls on the form and add a new record for each. So, yes it can be done, but you can accomplish the same thing by using a form in datasheet view (with 1 control) and the user would just enter their text on each row to enter a new record. Could you provide some more detail on what you are trying to accomplish?

 

[The Bey]

I'm trying to get 4 date values from 4 different combo boxes into a table so that I can then run another query to select which of these dates is closest to the current date.

In another post somebody mentioned looping but I don't know how to do this. Any suggestions?

 

[jzwp22]

First, why would you want to store the dates in a table? What do you plan on doing with the date that is closest to the current date once you identify it?

 

[The Bey]

With the date that's closest to the current date, i want that to be displayed as the time when an action needs to be put out.

Basically, there're 4 possible actions, each one is assigned a date and the date which comes around first will be the most important time.

This sounds like i'm going in circles but how can I get a textbox to display another textbox's text, using code?
Before you dismiss this, my reasoning is that there are potentially 2 text boxes for each action, and depending on the criteria, I want it to show the relevant text.

I tried using "If criteriaMet Then Me.txtTextBox = forms!formName!txtTextBox" but it didn't like it. How can I go about this?

My dates have stopped working anyway, so it looks like i'm back to square one

 

[The Bey]

Turns out I was being a fool and my dates are still working...

 

[jzwp22]

If you have "something" that has 4 possible actions and a date associated with each action that tells me that you have a one("something")-to-many(actions) relationship. If that is indeed the case you need two tables as follows. I do not know what the "something" is so I will make this generic:

tblSomething
-pkSomethingID primary key, autonumber
-txtSomething

tblSomethingActions
-pkSomethingActionID primary key, autonumber
-fkSomethingID foreign key to tblSomething
-txtAction (a field that describe the action that needs to take place)
-dteAction (the date on which the action needs to take place your date controls on your form would be used to hold these)

Each action would be a separate record in the tblSomethingActions.
You can organize the records shown in the subform by the date you enter
With the above design, your main form would be based on tblSomething and you would have a subform based on tblSomethingActions.

Can you please provide more details on your table structure (tables, fields & table relationships)?

 

[The Bey]

To be honest I haven't really used relationships and have tried to avoid them by using code and not creating too many tables.. I'm guessing it's the wrong way to go about it but I don't understand relationships and things so I've tried to stick with what works for me.

I was trying to go for something along the lines of 4 different tables, 1 which could relate to each date and then using a query, run the output from the form to specify criteria on the table. i.e. table headers; ID, Action, DateInMonths, HC and Material(these are abbreviations). Action, HC and Material come from the form and the date will be selected.

This would be done for each of the 4 dates and then I would like the date output in months against the current date to give me a projected date, then the dates could then be run through a second query to group them and thus choose the one I want. This is rushed as I'm leaving work but I can respond at home, just not run access

 

[jzwp22]

To be honest I haven't really used relationships and have tried to avoid them by using code and not creating too many tables.. I'm guessing it's the wrong way to go about it but I don't understand relationships and things so I've tried to stick with what works for me.

Avoiding relationships and avoiding having too many tables as you said is not the way to go with Access. The key to a successful relational database application whether it is in Access, SQL Server, Oracle or MySQL etc. is the table structure. The table structure is the foundation for everything related to the database and how it operates. Avoiding a proper table structure will force you to find unconventional ways to solve problems, but eventually your application will fail.

OK, I'll now get off my soap box...

If you are willing to learn, we are willing to help you design an appropriate table structure.

The first step is familiarize yourself with the rules that provide the back bone for the table structure: normalization.

The next step is to analyze your process and the data associated with it. During this analysis is where you would look for and uncover the relationships in your data/process which will help you to build your table structure.

 

[The Bey]

Well it's not quite true about the tables.. I've actually created far too many, but in this form that I'm spending so much time on, I'm doing a lot of it using code (not complex code, just If-Elses's as they seem to be working fine).

In a previous form I have a number of combos which are individually using seperate combo boxes to get their data, so that data is pretty much set in stone and can't be edited. I don't get the benefits of using relationships, but I'll work on it.

Can I make a date, just by reading a number from a table (6 for example) ? Something in which the number is converted to a month figure and superimposed upon the current date, or a date of my choosing?

 

[jzwp22]

Can I make a date, just by reading a number from a table (6 for example) ? Something in which the number is converted to a month figure and superimposed upon the current date, or a date of my choosing?

I'm not clear on what you are asking here. If the number is in the range of 1 through 12, you can construct a date with the number representing the month but you still need the day and the year. The dateserial() function can be used

=dateserial(2011,yournumberfield, 1)

If the number in the field is 6 as in your example, the dateserial function above would yield 6/1/2011 (mm/dd/yyyy)