Filling table from form using only ID'S

[bloukopkoggelmander]

Right here is my problem.

I have various tables ie.

NL Clients(ClientID,ClientFirst,ClientLast, Address,Postcode etc.)
NL Treatments(TreatmentID,Treatment)
NL Costs(CostID,Cost)
NL Client Treatments(ClientTreatmentID,ClientID,TreatmentID, CostID,Date)

Right I already have three working forms set up.

The first is to enter new clients and this works fine.
The second is to enter new Treatments and this works fine
The third is for entering new costs and this works fine.

The forth form is the one completely throwing me. What I want on this is as follows :
-Client Name ( Concatenated First and Last Name. I have already set this concatenation up via a query.
-Treatment ( A drop down menu with all possible treatments to select from)
-Cost ( A list of all possible costs to select from)
- Date ( A calender to select the date from)

Now I have tried so many times to set this up, but I just cannot figure out how to, once the above selections are made to save only the related ID'S to the NL ClientTreatments table. Hope this makes sense.

I will try and attached a copy of the database.

[NeoPa]

Right, I've found the new thread and will look through it more thoroughly when I get home.

[NeoPa]

Right, I've found the new thread and will look through it more thoroughly when I get home.

Sorry, I've got the database downloaded but had no time to look at it yet.
Will try again tomorrow.

[bloukopkoggelmander]

Damn, dont you ever sleep? Just looking at the time of your last post.

Listen, no worries. I think we let this one go till Monday. Have a good weekend.

[NeoPa]

If that's late for you, you should look at some of Mary's post times (She lives in Dublin btw so same time-zone as me). I was chatting with her last night and we were looking at some 'difficult' threads that needed attention. This can slow up our ability to respond to the better behaved posters, which irritates me somewhat I have to admit. Having said that, many are just new to the whole idea and start slowly, often learning in time to communicate better and work much better with the experts. It's good to see when that happens.
Anyway, I'm rambling... I'll try to get to your new database over the weekend sometime.

[bloukopkoggelmander]

Howzit Neopa

You still there?

[bloukopkoggelmander]

Howzit, does anyone has an idea for a solution to the problem I have?

Thanks

[NeoPa]

Sorry man.
I let this one drop off my radar. Will get back on to it tonight (Even though Spurs are playing live on the telly tonight).
I really can't imagine how I dropped this one. I thought I'd replied already.

[bloukopkoggelmander]

No worries man.

Unfortunately I am not a football man, but go ahead and watch the game cause I am aslo working on another huge database anyway so it will keep me busy and then you can get back to me later in the week.

Enjoy!

[NeoPa]

After dropping the ball and letting you down so badly, I think I need to revisit this when I get home.
From memory, I've already looked at it and found a problem, I just don't know why it was never posted. I appreciate your patient attitude though.

[kadriokur]

Right here is my problem.

I have various tables ie.

NL Clients(ClientID,ClientFirst,ClientLast, Address,Postcode etc.)
NL Treatments(TreatmentID,Treatment)
NL Costs(CostID,Cost)
NL Client Treatments(ClientTreatmentID,ClientID,TreatmentID, CostID,Date)

Right I already have three working forms set up.

The first is to enter new clients and this works fine.
The second is to enter new Treatments and this works fine
The third is for entering new costs and this works fine.

The forth form is the one completely throwing me. What I want on this is as follows :
-Client Name ( Concatenated First and Last Name. I have already set this concatenation up via a query.
-Treatment ( A drop down menu with all possible treatments to select from)
-Cost ( A list of all possible costs to select from)
- Date ( A calender to select the date from)

Now I have tried so many times to set this up, but I just cannot figure out how to, once the above selections are made to save only the realted ID'S to the NL ClientTreatments table. Hope this makes sense.

I will try and attached a copy of the database.

hi very thanks

[NeoPa]

hi very thanks

:confused:

[bloukopkoggelmander]

No problems man. I am from Cape Town, so am as chilled out as it gets....:-)

[NeoPa]

I'm preparing a response as I post this, but I though adding a link to the other thread (Table Relationships ) would be a good idea at this point.

[NeoPa]

No problems man. I am from Cape Town, so am as chilled out as it gets....:-)

Right,
Let's start with a lecture about the importance of design :D
This is because I can see that you are suffering from issues avoidable with properly thought out design (Not to say it's bad particularly, but better would have avoided some confusion for you later on).
First, have a read of this tutorial (Normalisation and Table structures) - understanding of this is REALLY helpful (Trust me on this). Your db doesn't fall particularly foul of these issues so you may already have a fairly good understanding - but I'd read it anyway. I did, and it wasn't a waste of my time.
Now, the main point I want to make is about using meaningful names.
'What?!?' I hear you ask - 'Is he serious?'
I certainly am. Cosistency of naming and selecting the correct terms can be so helpful in a project to help you understand where you are and where you want to go. Don't underestimate this.
You have four tables :
NL Client Treatments
NL Clients
NL Costs
NL Treatments
The last of these is a list of Treatment Types not a list of Treatments (As I understand the word to mean).
It is usual (and took me a little by surprise) for a table named [NL Client Treatments] to be a linking table to match Clients to Treatments in a Many-to-Many relationship (NB The link between a Client and a Treatment would, itself be unique). This is not the case in your database. This is really a list of Treatments (Not Treatment Types) and in this case (I presume), there can be multiple treatments, of the same type, for any client.
From this point, as I have seen and I think I understand, the layout of your db I will try to use terms (Names) currently used in your db. In fact, to separate this from the direct answer I'll post that separately.
When you have your tables designed (actually, with a closer look, the design is not too bad when you understand the naming used), Add the relationships into the Relationship Window, this way you can control various things that the db can check for you automatically, thereby catching potential problems early.
When that's all done, create your forms etc and build up the application.

[NeoPa]

Right, down to the specifics of building your form.
I was planning on getting complicated here, but it seems the wizard will do almost all of it for you.
I'll take you briefly through that and you can then tell me where you want to go from there if you're not completely satisfied.
Firstly though, you should lose the [ClientName] field from the [NL Client Treatments] table.

1. Select the table [NL Client Treatments].
2. Insert / Form.
3. Form Wizard.
4. >> (Select all fields).
5. Columnar.
6. Standard.
7. Change name of form to... (Suggestion = [frmNLClientTreatments]) and select 'Modify the form's design'.
8. Finish.

That should be, pretty well, what you want.

[bloukopkoggelmander]

Hi Neopa

Well, hope the football went well for you last night.

Thank you for the massive response. Yes I have read the topic on Normalisation before, but would clearly have to go through it a few more times again. I have read throught everything with great attention and I will try my best to get my understanding of it all up to standard.

Right for the last post you made, I have followed it to the point and it does create the form with the relevant fields, but it still has the same problem as before where all boxes only present me with choises of ID's which is not very user friendly. This is going to require the person using the database to memorise the id's with thier relevant descriptions to know what they mean.

So as you can propably gather, I am aiming to have frmNLClientTreatments to show user friendly options in the combos to select. An this is what I cannot get my head around how to do.

Hope this makes sense.

[NeoPa]

For each ComboBox :

1. Go to the Row Source and add the display item to the SELECT clause. This gives a multi-column ComboBox.
2. Set Column Count to 2
3. Change Column Widths to 0cm;Xcm where X=width of ComboBox.
4. Leave Bound Column as 1.

Let me know how you get on with them all.

[bloukopkoggelmander]

Hi Neopa

Right I have followed your instructions and all combo boxes are now showing ID and description and when selecting the relevant data, only the ID is then entered into NL Client Treatments Table.

So I thank you very much for your patience and all your help.

Just one question though. Will the way I have all this set up work ok when it comes to creating reports?

Thanks man

[NeoPa]

Not a problem.
However, I'm not sure I understand that last question. Why would it affect reports in any way?

[NeoPa]

Right I have followed your instructions and all combo boxes are now showing ID and description and when selecting the relevant data, only the ID is then entered into NL Client Treatments Table.

I missed the significance of this earlier. It may not be important for you, in which case don't worry, but the instructions I gave would have left you with a ComboBox which didn't show the IDs at all.

[bloukopkoggelmander]

Howzit Neopa

Sorry I did not get back toy uo on Friday, I went home early from work.

Right back to the last post. When I select a combo box now, I have a dropdown list that shows ID,(Description). When I then select the option I want, the ID is entered into the combo box. This is not too bad because at least now I can see what description each ID relates to.

I have attached a screenshot to show you what I mean.

Sorry, it did not work I will just try and send the database again.

[NeoPa]

OK man. That's fine. If you want it to show the ID as well then clearly that's possible. I only set it trhe other way as that's what you'd asked for earlier.
Is it all working as hoped now then?

[bloukopkoggelmander]

Yip all seems to be woeking fine so far. I am now just trying to get my head around creating reports( which is not very successfull). But I thank you for all your help with this problem.

No doubt I will be back with some more issues in the very near future.

Thanks a lot man

[NeoPa]

No problem.
I'll PM my next comment.