Combo Box

[REAPER_110]

Hi,

Could anyone help me with the attached file please. On the form frmPeople I changed the box that the wizard generated to a combo box but don't think I have got things quite right as a 0 appears in the form view and it doesn't save the relevant record in the table tblTitles so wont allow it to save!!

Also, on the frmPeople in form view the Client ID box starts off with "AutoNumber" in it. Is there any way to get that to appear blank until a record is starting to be created then the generated number appears?

Kind Regards,
REAPER_110

[jzwp11]

Could anyone help me with the attached file please. On the form frmPeople I changed the box that the wizard generated to a combo box but don't think I have got things quite right as a 0 appears in the form view and it doesn't save the relevant record in the table tblTitles so wont allow it to save!!

You did not bind the combo box to the foreign key field (fkTitleID) related to title in the form's underlying record source. I corrected that.

You also had issue with the advisor and language controls. They should both be combo boxes. You had the advisor tied to the fkRoleID field, the tblRoles should be generic roles people play not specific people. All people should be in tblPeople. tblRoles should include things like advisor, client etc. I have corrected both combo boxes.

The advisor is assigned in the client interview table, so I created a subform (for illustration purposes) in frmPeople and limited the records shown in the form to clients. I used a combo box in the subform for advisors by basing the combo box on a query that limits the records to only those people with a role of advisor. Hopefully you will be able to follow what I did.

[REAPER_110]

Hi mate,

What I need is....

On the people form, to be enter the name of an advisor, I dont need to be able to select client or advisor. This will be the name of the advisor allocated as the main advisor to the client.

I like your interview subform Will be able to use that nicely I think

The advisor on selected on the people form will be the main advisor for any queries, then on the interview the advisor selected will be the actual advisor taking the interviews, 99% of the time should be same as main advisor but how you have done the interview part will allow a different advisor to be selected if for example the main advisor is absent.

What do I need to change to get the database as described above and also how can I stop the autonumber being displayed on the people form? When I say this I mean can it be blank until information has started to be entered?? Instead of saying "Auto Number" before the form has been started?

I see its deleted from the people form, I dont think I explained properly, I need the auto number displayed on the form but before any data is entered it says "Auto Number". Is there anyway to get this box to appear blank until the form is being filled out at which point the auto number itself will appear?

Kind Regards,
REAPER_110

[jzwp11]

What do I need to change to get the database as described above and also how can I stop the autonumber being displayed on the people form?

Regarding the primary advisor, if there will be only 1 primary advisor ever, then you would need a fkPeopleID in tblPeople to link the primary advisor to the client. You will then need to add a combo box similar to the one I used on the subform in you main form.

Regarding the autonumber control on the form, just hide it altogether. The primary key field is only there for Access & should have no significance to your users, as such, your users should never see it. I did change the visible property of the pkclientID control to no (i.e. hidden) so you should not see it when you open the form normally. I did not delete it.

If you need some sort of reference number to uniquely identify a client that would be another field in the table (and thus another control on the form).

[REAPER_110]

Hi,

Yes, the client would need a unique reference number to them. My idea is to have a main screen where the adviser can search for the client using either name, NINO or client reference number. This is because not all clients have their NINO with them when they first attend and also there may be more than one person with the same name so the adviser can then use the unique client reference number to bring up the clients details. Am I presuming from what you have said then that I need to add a client reference field into tblPeople and set it as auto number?? Is there a way to get the reference number to be a set of random generated numbers and letters??

For example BBC3456B??

Kind Regards,
Andrew.

[jzwp11]

Am I presuming from what you have said then that I need to add a client reference field into tblPeople and set it as auto number

You can have only 1 autonumber field in a table and we have reserved that for the primary key field, and you cannot increment a text field (BBC3456B) at least not directly. There are ways of creating a field that you increment with each new client. How are you assigning clientID's now (not in your database but in your business/organizational practice)?

[REAPER_110]

At the moment we have an old system that is internet based that I dont have access to coding for. Basically the new database I am working on is hush, even from the IS guys at the moment until we have the new one working so we can present it to them. At the moment the system generates a number for each client like BBC3456B. It is random and thus unique for each client. This is giving me a headache! lol. Thanks for all your help, it is greatly appreciated.

[jzwp11]

You did not mention that you were planning on replacing a web-based DB. You do realize that only Access 2010 can technically be web enabled via Sharepoint (you have to have Sharepoint Services), but the earlier versions have to be "migrated" in some way (some companies offer this service-for a fee). Access tables are easily converted to other DB software packages, but the front end (forms, queries, reports etc.) i.e. the "user interface" generally has to be rebuilt using html in combination with Visual Basic (not Visual Basic for Application) or C#, C++ etc. You may want to consider that before you get too far into designing forms...

[REAPER_110]

Its ok, we did know that. Our team is basically being overseen by the Chief executive and this is what she has asked us to do. She wants it changed to what we are working on. The idea is that our database will be split as you said, front end and back end. She wants the advisors issued tablets so that the front end can be put on to them. The tablets will have internet capability to connect to our back end server. The idea is that the advisor can then sit with the client and the tablet, thus removing the need for a desk and removing that barrier between the clients and the advisor. The chief exec wants our team to complete this database so that it can be put in place to remove the old system. She has said that once this is up and running and we can prove its working she can then talk to the IS guys and get them to change anything required if need be. My team is just working to get this database running so that she can present it.

[jzwp11]

OK then back to your question, you can assign a sequential, unique number to a client (which I have done before). I suppose you can assign random numbers as well but that is not something I have tried. To assign sequential numbers, you would use DMax()+1 in the before insert event of the client form.

[REAPER_110]

ok, I'm being thick! Again! lol. Do I add another column into the tblPeople to hold this information? Then add a label onto the form and insert that into the label information?

Kind Regards,
REAPER_110

[jzwp11]

Yes, you would need to add a new field to tblPeople and a control to the form. In the before insert event you will have to determine the highest value and add 1 to it. You will actually not see the new client reference ID on the form since it will not be established until the record is saved.

Since you will only want to increment those people who are clients you will need a query to use in the DMax() function

The query would be something like this:

SELECT yournewfield FROM tblPeople WHERE pkRoleID=x

x=primary key value applicable to the role of client which in the database I posted yesterday =2

The code in the before insert event

me.newcontrol=DMax("yournewfield","queryname")+1

[REAPER_110]

Cool, got it Will sort that out now. Is there a way to slightly change it so that the number is generated as the form is being completed and displayed within the form as its being completed so that the advisor can make note of the clients number?? This will be written onto the client file so it easier to find within the filing system. Also, when I create the contacts form to add to the frmPeople as a subform, I just want 3 fields, 1 for phone, 1 for mobile and 1 for email.

How would I use the tables you have already sorted for me as I dont think thats how it works at the moment is it??

Eventually we also want to add mail merge. By this I mean the advisor can use an interview appointment letter for example and the name, address and everything can be taken from the database. I am right in thinking that this it totally do-able yes?

Kind Regards,
REAPER_110

[jzwp11]

I did some playing around and it will show up when you start typing in the first field of the form. If you do not want to show it, keep the control hidden until you have most of the information about the person added. You can use the after update event of one of your required fields to make the control visible again. As a note, you will have to take care of the first new client by defaulting the Dmax to 0 and the add 1 to it, so the code I presented earlier will need the following modification (I've substituted my own names below; you will have to replace them with your names)

Me.ClientRefID = Nz(DMax("clientrefid", "qryClientRef"), 0) + 1

I just want 3 fields, 1 for phone, 1 for mobile and 1 for email.

They should be 3 records not fields.

Eventually we also want to add mail merge. By this I mean the advisor can use an interview appointment letter for example and the name, address and everything can be taken from the database. I am right in thinking that this it totally do-able yes?

Yes. You can probably do a search on the forum for examples.

[REAPER_110]

Hi mate,

Just a quick question. Which of the contact tables do I need to use to make the contact subform to be inserted into the main frmPeople?

I want it so there are 3 boxes to be completed on the main form?

I know you set up 2 contact tables. Contact methods and contact types.

Kind Regards,
REAPER_110