From Forms** combobox select services, services be saved in row for each customer. (2 Viewers)

[Shoebnisar]

Dear All,
Is it possible from forms combobox select services, the value be stored into tables row wise, may be up to 10 items (horizontally)...? And for every customer the services may differ... for some it may be upto 5, and for some it may be 6, 7, 4 any thing...

Example

Client ID	Client Name	Service 1	Service 2	Service 3	Service 4	Service 5	Service 6
xyz	abc	def	ghi	jkl	etc	etc

[td]

1​

[/td]​

 

[Uncle Gizmo]

That is the Excel way of doing things and it doesn't apply to MS Access...

If you do go that route you will quickly run into problems which are practically impossible to solve!

To model that arrangement you've shown there correctly in MS Access the first thing you do is divide it into two tables...

One table for the client details and another table for the service details....

 

[Gasman]

Excel is wide and shallow sheets.
Access is narrow and deep tables.

Do not go the first route in Access. You need to rethink.

 

[DHookom]

Dear All,
Is it possible from forms combobox select services, the value be stored into tables row wise, may be up to 10 items (horizontally)...? And for every customer the services may differ... for some it may be upto 5, and for some it may be 6, 7, 4 any thing...

Example

Client ID	Client Name	Service 1	Service 2	Service 3	Service 4	Service 5	Service 6
xyz	abc	def	ghi	jkl	etc	etc

[td]
1

[/td]​

I think Shoebnisar is not being understood. I read the input screen would look like a spreadsheet but the data would be stored “row wise”. You can write some VBA to append as many records as have been entered into the combo boxes. It would be a basic loop through the combo boxes that I would name with a numeric suffix.

 

[June7]

As Duane said, it is possible to have a "non-normalized" form for data entry and save the data into "normalized" table by use of VBA. But why? Are you certain that 10 items is the forever upper limit?

 

[plog]

Why do you want it like that? Typically a 1 to many relationship (as in your 1 Customer to many Services) is done with a subform that scrolls vertically like so:

ServiceNumber	ServiceType
1	abc
2	def
3	ghi

If you have more services to add to a customer, just keep adding new records. If you have less you don't have a ton of empty records on the screen. Use just as many rows as you need--no more no less. Super easy to do with forms in Access.

What you described is not impossible but very difficult to achieve. Why do you want it the way you asked for?

 

[Uncle Gizmo]

I think Shoebnisar is not being understood.

The op has started three posts which have all received responses but the op has declined to comment so far...

Hence I am not to prepared to answer the op question in detail until I detect a real live engaging person...

 

[Pat Hartman]

@Shoebnisar Let me try to address this a different way.

You seem to understand that you don't want to store the services in columns but instead as up to 10 individual rows. Great. But why do you want to hard code a limit on your data entry form? Granted, if you have a customer who has 11 services, your maintenance is limited to only the data entry form so this isn't terrible. Actually storing the services in columns would be terrible but this request is only going to create extra work in one particular place. I still wouldn't do it. I would use a subform that shows a list.

I'm guessing that the problem you are trying to overcome is that you want a DS style form with a customer on each row and due to the way the forms work, you can't add a subform to the continuous subform so you want to show the services as columns to get around this.

So, let me make a suggestion. Don't use this form view as your data entry form. Let this be a form based on a crosstab query which is not updateable. Then have a single record form which is used for data entry. It can use the list type subform which in and of itself will save you a lot of coding. Then in the double click event of the customer ID, you open up the data entry form as model. Model is important because you want the code in the non-updateable form to stop running when the popup form is opened. That allows you to make changes to services, which probably have other information such as a start and end date, etc. Then when you close the popup, the code in the calling form continues on the line after the OpenForm method and you can requery the calling form to pick up any changes. There's a little more to this technique since you probably want to stay on the record you just updated but we can address that if this idea appeals to you.