MS Access Database Help

andrests

Hello,

I was wondering if perhaps someone can help me with a MS Access database I have.

I have a Table that is receives entries from a Form. The field in the table is entered from a Combo Box Drop Down List. Here is what I need to do; I am trying to select more than one of the options in the drop down list and enter a all those entries in the table field, of course, the table field would need to be modified so it is can accept more than one entries.

Here is how I can visualize it:

From A

Drop Down for Field3
Option1
Option2 x (Selected)
Option3
Option4 x (Selected)
Option5 x (Selected)
Option6

Table A

Field 1 Field 2 Field3

Entry 1 Entry 2 Entry3a

.................... Entry3b

.................... Entry3c

Other1 Other2 Other3
Other1 Other2 Other3a
.................... Other3b
Other1 Other2 Other3

I hope the idea is easy to understand, if any one can help me I would really appreciate it.

Thank you in advance,

andrests-----------------------

pete_bisby

There are a couple of ways you can do this.

The first is to have the table exactly as you have it in your example, with the three fields. Problem here is that you'll duplicate the data in field1 and field2 for as many times the user has selected options within the drop-down box in field3 in your form.

The other way would be to place the information for field1 and field2 within its own table, and create a primary key in this table for these two fields. Then enter your information for field3 with the primary key into a second table. So your table structures would be:

Table A

PrimaryKey Field1 Field2

Table B

PrimaryKey Field3

Data For Table A

KeyA Data1A Data2A
KeyB Data1B Data2B

Data For Table B

KeyA Data3X
KeyA Data3Y
KeyA Data3Z
KeyB Data3X
KeyB Data3Z

Hope this makes sense !!!

andrests

Sorry, it seems to me my original explanation was not accurate enough. Still, thank you for your reponse.

Let me try again; I have a DropDownBox where I can select Opt1, Opt2, and Opt3. When I select one click on one option with the mouse, it gets highlighted and then that option is entered into Field3 in TableA.

What I need to do, is to be able to select one option, highlight that option and be able to highlight more options. When I am done selecting options, then all those options I selected should be entered in Field3 in TableA. I would like to set Field3 as a field with inner fields, so each selection is entered in an inner field.

I know this is pretty confusing, thank you for your help,

andrests

pete_bisby

So you want to select more than one option within a <select> drop-down box, and post these values?

<select name="Field3[]" size=5 multiple>
<option value="Opt1">Opt1</option>
<option value="Opt2">Opt2</option>
...
</select>

You make the <select> an array with the inclusion of square brackets, and to select more than one option include the 'multiple' keyword within the <select> statement.

When this is posted PHP will create an array. Create a simple loop to traverse the array and to enter the selected information into your tables.

for ($i=0; $i < sizeof($Field3); $i++) {
// code for entering data into tables
// something like - "INSERT INTO Table1 (Field1, Field2, Field3) VALUES ('$Field1','$Field2','$Field3[$i]')";
}

andrests

Hello,

Thank you once more for your help, unfortunately, I am still confused how to use the code you provided me in your last reply.

Now that you understand the situation better let me be more specific. Here are the db elements I am dealing with:

Table Name: Active Media
Field Name: Dist Method

Query Name: Active Media Dist Method Query
Query Contents: HD, Racks, Mail, Carriers

Form Name: Active Media
Control Box Name: Dist Method
Control Source: Dist Method (Bounded to Active Media Table)
Row Source: Active Media Dist Method Query

Now, here is how everything is working right now, when I open the Active Media Form and go to the Dist Method Control Box to enter a new record in the Active Media Table, I press the down arrow in the right site of the box and I get the contents of the Active Media Dist Method Query to choose from.

Once I click on one of those contents, let's say HD, then it gets selected and the drop down goes away, HD is assigned to the Control Box and HD is entered into the Dist Media Field in the Active Media Table.

Now, here is what I would like to be able to to, once I press on the down arrow on the right side of the Dist Method Control Box in the Active Media Form and the contents of the Active Media Dist Method Query are shown in the drop box, I would like to click on one content while pressing some key like SHIFT or ALT and as long as I keep pressing that key, then I can select other contents in the drop down box, those that are selected will have a back background.

Then, when I let go of the key, all those contents will be entered in the Media Dist Field in the Active Media Table as an array of contents in the same field. Some records in the table may have entries in the Media Dist Field of one content and some other record may have entries in the Media Dist Field of more than one content.

I think this time I was really specific, however I did not want to waste your time any longer. Unfortunately, I am new on MS Access and MS Visual Basic, that is why I could now understant how or where to write the code you send me in your previous replay.

Thank you so much for your help and patience,

andrests

pete_bisby

I think this is probably confusing, because this has nothing to do with PHP or MySQL.

Anyway, I think you will need to use a List Box and not a Combo Box, because Combo Boxes will not allow you to select multiple lines.

Once the List Box has been selected, you will need to make sure in the properties that Multi Select is turned on. You will be able to select the items with a single click, or by pressing the space bar. There is an Extended option, which allows you to select items within the List Box with the CTRL key or Shift key in the same way you would select items within Windows (dirty word around these parts!!!).

Now the following piece of code has been placed behind the Click event of a button within an Access form. I have a form called Form1, and a List Box called List2 (original names, I know!), just for an example:

Private Sub Command4_Click()

Dim Item As Variant
Dim intI As Integer

For Each Item In Forms!Form1!List2.ItemsSelected
For intI = 0 To Forms!Form1!List2.ColumnCount - 1
MsgBox Forms!Form1!List2.Column(intI, Item)
Next intI
Next Item

End Sub

This will allow you to view the items that you have selected within your List Box. All you will need to do from here is place a simple INSERT query to enter the items you have selected into another table, something like:

Docmd.RunSQL "INSERT INTO tableName (Field1, Field2) VALUES (Data1, Data2)"

andrests

Hi, thanks for your help.

Now I am almost there, I managed to create the list box and use it to select one or more items from the list box.

I understand you advised me to use an INSERT QUERY to enter the data from the list box. The problem with that is that it would only allow me to enter one of contens selected from the list box.

I would like to enter all the contents into only one field as an array or contents. When I set up the table, I have the field that will take this contents as TEXT, but I would like to set it as a TEXT ARRAY of some determined number of entries, but I do not know how to do that.

For example:

List Box (Bound to Field3 in TableA)
|----------|
| Option 1 | X Selected
| Option 2 |
| Option 3 | X Selected
| Option 4 | X Selected
|----------|

This is how I want the contents to be entered in the same field in the TableA

TableA

|Field1|Field2|Field3|Field4|Field5|

The dots "....................." I only typed them because I needed to create space for the indentation but spaces are ignored by the editor.

Where Other is the data that is already in the table and the data entered after the example record. So, all those contents selected in the list box are entered into the Field3 as an array of contents. While other records in the same table may or may not have an array of contents for Field3.

I hope what I want to do is possible. Thank you so much for all your help,

andrests

pete_bisby

You said that it would only allow you to enter one of the contents from the List Box.

If you place the INSERT query within the For loop, it will run for each content selected.

You shouldn't need an array, because you're saving the information within a table. I think you're making things slightly difficult for yourself if you attempt to save arrays within fields in tables.

You have a relational database - use it to it's full advantage.

andrests

It seemed to me that using this INSERT statement would insert Data1 in Field1 and Data2 in Field2.

Docmd.RunSQL "INSERT INTO tableName (Field1, Field2) VALUES (Data1, Data2)"

However, I want to enter Data1 in Field1 and Data2 in Field1 also without loosing Data1. That is why I was talking about an array, so Field1[1] = Data1 and Field1[2] = Data2. It seems to me this is something too complicated to do.

What I did was to create another table that refers to the original table using the primary key. That sencond table has one column for the primary key and 5 more columns for all the data generated by the list box.

My problem now is that I can not use the INSERT statement correctly. I assign each list box output to a string variable and then use that string variable inside the INSERT statement. My problem is that the INSERT statement is entering in the table the name of the variable instead of the value of the variable which is what I want to enter in the table.

Here is how my insert statement looks:

DoCmd.RunSQL ("INSERT INTO [Active Media Competes with] VALUES(entry, NULL, NULL, NULL, NULL, NULL);")

where entry is the variable, and its internal value is what I want to insert in the table. Can you tell me how to write the statement so I can insert the value in the variable entry. This will allow me to populate the primary key and later on the 5 contents from the list box.

Thank you for your help, I know this is becoming such a long problem, thank you for your patience.

andrests

I just wanted to thank you for your help, I finally got it working. I created another table that is related to the one I was working with, now that table will take all the entries from the list box in it, and by being related to the other, it acts as a one table field with many fields inside of it.

Thank you for your great help,

andrests