Hvordan gemmer man nul til flere valg i et felt i tabel

Feltet i TblProject, hvor værdierne skal gemmes hedder ProjectType

Do I understand the question correctly. You want to be able to store more than on boolean value in a single field in another table?

If thats correct then you could maybe make a function which stores the values and another which retrieves them again.

You could storethem in a text field seperated by say a ; or ,


For example to insert you could use.

Docmd.runsql "INSERT INTO YourTable (SomeTextField) VALUES ("'" &  Me.cbx1 & "','"  & Me.cbx2 & "')"


If you need to retieve them too then I'll fin dsome code for that too, but I'm off out now.

mvh
Terry

Jeg er ikke sikker på at jeg forstår, hvad det er du vil?

Hvis jeg forstår det rigtigt, skal du bruge TblProjekt med feltet ProjektType. Feltet skal hente sine værdier fra et felt i TblProjektType. I dette felt er der værdierne IPP, EPC TSA, Rehab osv og du skal tilføje værdien 0.

Hvis det er forkert, må du prøve at forklare nærmere.

INSERT INTO TblProject ( ProjectType )
SELECT [IPP] & "," & [EPC] & "," & [TSA] AS Expr1
FROM TblProjectType;

Trine can you give a little more information as to what you want to do, we may be able to make some suggestions.

Ok, I have a form FrmProjectData, based on the query QryProjectData, based on the tables TblProject and TblClient.
On FrmProjectData I have some checkboxes: ChEPC, ChIPP, and ChTSA. Their controlsource is ProjectType whice is a field in TblProject. I would like the user to fill in from zero to all three checkboxes, and to store the value/values separated by a ; in the field ProjectData in the table TblProject

Sorry I meant the field ProjectType in TblProject

Sorry Trine but I dont think I quite understand what you have and what you want.

You say you have some checkboxeswhich alreday is ProjectType, but you want to save the selected values in the same field. Is that right?

As far as I can see the data is comming from tblProject

I suppose I need to use an IF or Select sentence, for it is only if the checkbox in question is clicked that we need to store the value in the field ProjectType.

Trine why is it neceesary to put the values in one field? A checkbox can be bound to a field in the table just like any other field.

If you MUST save the values of the checkboxes in only one field, and you also need to show the values later, then you MUST save a value for ALL checkboxes  so that you can do this.

>Terry, Yes I can understand your confusion, but it's because I haven't worked with the user having to fill in more than one checkbox and store the value in one field before. I started out having the values in TblProjectType, but that was because I usually make the user choose one of the values from a combobox and then store it in the Main table in this case the field ProjectType in TblProject.
>Hekla, det ser rigtigt ud sådan som du har forstået det hvis du med 0 mener True, men hvordan gør man så?

Yes I am a little confused :o)
But why is it necessary to put all the values in one field?

Can a project have more than one type?

If that is correct then you should have an extra table between tblProject and TblProjectType

Yes it's necessary to put from zero to 3 or 4 values in one field. And yes a project can be of more than one type. Don't ask me why :-) I think it's very strange.

Ok yes I can see that: Many Projects can have many ProjectTypes that's not very good. But If I dropped the TblProjectType and used my Checkboxes instead wouldn't it then be possible to code an IF sentence behind the FrmProjectData. Fx. OnClose.

Have you looked at the idea I gave 09/11-2005 18:44:56?

Yes I tried it, but the wrong place I think, but I will need to change it to an If statement, because it is only if the checkbox is checked that the value/values needs to be stored.

Trine, after the values are stored will these values be used later?

Is it possible for new project types to be added to the tableTblProjectType?

What values do you want to save in the table? 0 or the actual PorjectType for example IPP or EPC

I'm trying to fomd out exactly what your trying to do so that I can make some suggestions.

Yes, when the form FrmProjectData is reopened then the checked and unchecked checkboxes need to reshow what the user chose, and the user should then be able to change the choices or maybe append some more choices or uncheck some choices. As to what values I want to save it must be the actual ProjectType fx IPP or EPC. I can see that the easiest way now and here would be to make all the fields in the TblProject, but as there are from 7 to 9 or more different ProjectTypes it will be too many to store individually (and there are about 30 more fields in the table as it is now-all data that connects to the primary key ProjectNo so...)

Doing this as you say isnt very sensible at all, and will take a bit of programming. You will need to be able to INSERT/UPDATE/DELETE into the table. You will need code to check if a record exists to decide if you need to INSERT/UPDATE. You will also need to read from the table and set the values on the form.

They way I see it you need another table as I mentioned here 10/11-2005 13:18:44

Trine I would strongly suggest that you have an extra table to hold the project types you choose. THis will reduce the code needed to almost nothing.

The extra table just hold a relationship to your projects table and TblProjectType

Then make a small sub form where you can choose project types. The sub form should have a combo where you choose them from.

Hi Terry, Thank you for as always your quick and wise answers. I will probably follow up on your advice. But I dread the following Search picture. Please drop an answer so I can "point" you. And could you tell me. I am going to make a db which in one table alone is going to have about 185 to 190 fields all data which relates to one unique key. Is that possible? Do you think I should make a new question concerning this?

"....But I dread the following Search picture ..."? What search picture?
It must be easier to search on a normalised database than on a field seperated by , or am I missing something?

I think your limited to 255 fields n a table. So you should be OK with the number of fields your talking about. But take a close look at what you are putting in these fiels to see if there are any values which should in fact be in other tables (normalize).

No need to open another question if we can anaswer it here.

Have a good weekend

Hi Terry, About the no. og fields, I haven't yet added them, because they are not completely decided on by the users, and I will of course try to normalize as we get along (The different kind of data will probably be made on different Tabs). About the ProjectType, I needed to get on, so I simply put 7 fields in the Main table, and added 7 checkboxes to the form (completely forbidden I know). But I can see now that I need to make a link table between TblProject and TblProjectType, as you suggested, so I will do that next week. (Thank you for being so strict). As to what search picture, I am quite sure, there will be a wish for one or more "Searh" forms later on in the DB, and Yes it will probably be easier to work with a normalized DB, no need to start out the wrong way. You have a very good weekend too :-)

Get back if you need more help.

mvh
Terry