Populating listbox values of userform dynamically in the Access table fields

I have the following code which works fine. It stores data in Access table using excel/VBA. Now the below code adds listbox values in fields 17 , 18, 19 etc . I want to get it done dynamically so that the code checks the field names and then populate the values. T the moment the code stores listbox values in field number 17 onwards but if more fields get added before 17 field number then the order will get changed the following code .fields(j+17).value will populate values in other fields .

I want the code to check for field names and then populate values. so it will check fields names
Pol1,count1
Pol2,count2
Pol3 count3
;
;
;

So the listbox has 2 columns Policy Number and Total cases. If the listbox has 3 records then the data will get stored in Pol1,Count1,Pol2,Count2,Pol3,Count3.

I hope I made it clear but still any questions do let me know.

Please Login or Register  to view this content.

Thanks

You're not doing this in the right way, you're making life hard since your database isn't normalized.

You need to add a second table for Polls, which contains the count and any other info relating to that unique Poll, this info shouldn't be in the table you are updating.

Your second tbale may consist of 3 fields:

Please Login or Register  to view this content.

This now lets you have as many polls as you want for the record in tblMain and you don't need to keep adding extra fields, you just insert another record into this table

Thanks Kyle. But my manager wants just one table and separate fields fro each policy number in that table. I have to get it done in way I explained in my first post. Is it possible? Thanks

Then your manager doesn't know what they are talking about, is wasting time, resources and costing money and you should educate them

Just create a view for them that shows them what they want to see, they'll never know its not a table

Kyle, In my case the policy number is not unique . The table stores process name, Unique Batch Number and then all the policy numbers and Total cases for corresponding policy number in one table.

Can I make it possible in one table?

No, you need multiple tables.

Every row in every table should have a unique reference, this shouldn't normally have anything to do with the data, it's just an identifier for the row.

A couple of questions so I can give you an idea of how many tables you need:

1. Does a single batch contain multiple policy numbers?
2. Are there multiple polls per policy number?