Microsoft Access 2007 Help

Cherry_Cola · 05-03-2011 02:08PM #1

Hi, I hope this is in the right forum as this seemed the most appropriate one.

At the moment I'm building a toxicology database in Access. What I'm trying to do however is to have a combo boxin the form with several values.

What I would like it to do is say if a particular value in the first combo box is selected, I would like another combo box to be produced with values relating to just that value.

So say I have a combo box containing the samples taken e.g. Hair, blood, urine, liver etc

When I select blood, I would like another combo box to be produced with options such as where the blood was taken from e.g. heart, subclavian etc

Apologies if I haven't explained it very well but any help would be greatly appreciated.

JonathanAnon · 05-03-2011 07:36PM

You will need to create a piece of Visual Basic code to handle what happens when the combo box changes... If you look up the properties of the combo box on the form you will see the onchange setting:

you then create a piece of code to control what happens when the Combo box changes... this will take a the format similar to below:

Private Sub combobox1_OnChange()
{
code for updating the options in other fields...
}

You should get a book on Visual Basic for Access.. It's easy enough..

Cherry_Cola · 06-03-2011 02:09PM

Thanks, I was hoping I wouldn't have to get into the coding side but I guess I'll have to give it a try.

Also, I have encountered a problem whereby I have entered about 2000 drug types into a combo box. When I try to make a selection in a table, all the drugs and values are there.

However when trying to make a selection from the combo box in the form, there is only about 100 of the drugs available for selection. Is there anyway to fix this?

Took me almost 2 days to enter them so don't want it to have been a waste!

Thanks again.

JonathanAnon · 07-03-2011 12:04AM

Well you've obviously had the confidence to create the database, so creating the code for the combo box is something you should not be afraid of.. You dont need to become an expert in Visual Basic, you just need to find out the functions required to do what you need it to do..

Regarding the combo box not showing all of your list, I'm not sure if there is a limitation on the number of items that can be on a combo box... Is the list truncated at a particular letter or can you work out any reason why some are being shown and others are not... ?

Cherry_Cola · 07-03-2011 11:03AM

JonathanAnon wrote: »

Well you've obviously had the confidence to create the database, so creating the code for the combo box is something you should not be afraid of.. You dont need to become an expert in Visual Basic, you just need to find out the functions required to do what you need it to do..

Regarding the combo box not showing all of your list, I'm not sure if there is a limitation on the number of items that can be on a combo box... Is the list truncated at a particular letter or can you work out any reason why some are being shown and others are not... ?

Thanks, it's just this database is one of about 6 projects I'm working on at the moment so my time is stretched to the max as it is

I've gotten a book on it now though so will get cracking on it again tonight.

Yeah the list stops after the chemicals beginning with "A". It shows the first value in the B list and then there's an "Á" symbol and that's where it stops. It all works fine in the table view though.

JonathanAnon · 07-03-2011 11:18AM

it sounds like it has an issue with the special character.. On a quick google search there, the limit seems to be 65535 items in the combo box, so I'm pretty sure it's nothing to do with a limitation of number of items... Try changing that special character to something else as a test and see if it lists past that item...

oh, and it's a good idea to use a naming convention when you are going to be coding... for example, for a combo box that contains drug items, the normal naming convention would be cmbDrugs.. It's not mandatory to do this, but it makes coding much easier.

I think if you wrote out pseudo code for what you want to do, and then find out how to do each bit separately, this would be the easy way to progress... But from what you say, I think it would involve create some sort of SQL query based on the value selected in combobox1 and then changing the rowsource value of combobox2 and using the requery so that it will carry the results of this query,

Cherry_Cola · 07-03-2011 11:27AM

JonathanAnon wrote: »

it sounds like it has an issue with the special character.. On a quick google search there, the limit seems to be 65535 items in the combo box, so I'm pretty sure it's nothing to do with a limitation of number of items... Try changing that special character to something else as a test and see if it lists past that item...

oh, and it's a good idea to use a naming convention when you are going to be coding... for example, for a combo box that contains drug items, the normal naming convention would be cmbDrugs.. It's not mandatory to do this, but it makes coding much easier.

I think if you wrote out pseudo code for what you want to do, and then find out how to do each bit separately, this would be the easy way to progress... But from what you say, I think it would involve create some sort of SQL query based on the value selected in combobox1 and then changing the rowsource value of combobox2 and using the requery so that it will carry the results of this query,

Thanks for all the help, really appreciate it. The character Â isn't something I entered in to the combo box, it just appears at the end of the combo box in the form so I'm not sure how to delete it, any ideas?

Yeah I'll try and get that sorted, it's a little bit daunting thoguh as I've never coded anything in my life!

JonathanAnon · 07-03-2011 11:50AM

Cherry_Cola wrote: »

Thanks for all the help, really appreciate it. The character Â isn't something I entered in to the combo box, it just appears at the end of the combo box in the form so I'm not sure how to delete it, any ideas?

If you could take a screen capture of this an upload it, it might help me to see what is happening... Usually when something like this happens, the best thing to do is to find at which item it stops.. then remove this item temporarily to see if it is isolated to this item, if the item contains something that is incompatible with combobox..

Make sure that you have the latest service pack version down for Microsoft Office. Access is far from perfect and you can come across horrible nonsensical bugs, but I've never come across one that I wasnt able to solve through either googling or applying a service pack..

Cherry_Cola · 07-03-2011 12:00PM

Here it is in table view and you can see it transitions from A - B with no problems:

This is the form:

JonathanAnon · 07-03-2011 12:08PM

okay well what's the next item after Baclofen.. can you look up the table behind it.. cos it seems that one of the fields in the record for Baclofen OR the record after it, contains something that Access does not like..

Cherry_Cola · 07-03-2011 12:28PM

Here is a list of what's after Baclofen:

Should I try deleting the 2 records following baclofen and see if it helps?

JonathanAnon · 07-03-2011 12:48PM

okay well from your table the only fields I can see are Drug, Therapeutic Level, Toxic Level, Fatal Level... but nothing in those fields seems like it would cause trouble... usually with database stuff, if it displays everything up to a particular record, then there is usually an issue with that record... I would temporarily remove Baclofen and the following record and then have a look at the combo box again.... try that..

Cherry_Cola · 07-03-2011 01:12PM

Sorry to be a pain but I'm trying to delete the individual values from the combo box but I'm having no luck at all.

I went onto the table in design view, selected Drug, then went into Lookup and Row source and tried to remove them there but it wouldn't let me.

JonathanAnon · 07-03-2011 01:19PM

Cherry_Cola wrote: »

I went onto the table in design view, selected Drug, then went into Lookup and Row source and tried to remove them there but it wouldn't let me.

It may be locked by some of the forms that you have open... Can you close down any open forms and just open the table on it's own...

Cherry_Cola · 07-03-2011 01:29PM

I have the table open on its own now but it still won't let me edit the row source. When I select a word and try to delete it says "the text is too long to be edited".

JonathanAnon · 07-03-2011 01:56PM

I'm not sure what that error means.. I used mainly Access 2003 when I was developing, and it's not an error I'm familiar with.. You will need to work through that yourself... The only advice I can give is that it would be my opinion that there is something in one of the fields of the record for Blacofen or the record after it that is causing the displaying of the list to terminate...

Cherry_Cola · 07-03-2011 02:07PM

No problem, thanks very much for all the help. I'll let ya know how I fare at it but I'll leave it for today as it's giving me a headache

Cherry_Cola · 09-03-2011 08:52PM

I'm actually tearing my hair out at this stage. I've tried several things to fix it none have worked so far!

Firstly, I went to the row source of the table and copied all the code in notepad. I then set up a new table with a lookup form and pasted the code in, in sections purposely leaving out the 4 files after baclofen. That didn't work as the drop down box came out really messy with numbers all over the place.

Today I said feck it, and started to manually enter all the data in again, this time leaving in the values after baclofen and went on until I finished the letter B.

Then I created a form to see if the combo box worked, and it did!! Displayed all the values entered even beyond baclofen. So I continued to enter data and just decided to stop after the letter D and low and behold, it doesn't work anymore and now the last value in the combo box is benztropine!

I'm out of ideas and really need to get this to work properly

JonathanAnon · 10-03-2011 01:04AM

I've sent you a private message

Cherry_Cola wrote: »

Firstly, I went to the row source of the table and copied all the code in notepad. I then set up a new table with a lookup form and pasted the code in, in sections purposely leaving out the 4 files after baclofen. That didn't work as the drop down box came out really messy with numbers all over the place.

Notepad doesnt havent the power to handle the formatting.. You should use Excel to do this sort of operatioin..

Today I said feck it, and started to manually enter all the data in again, this time leaving in the values after baclofen and went on until I finished the letter B.

Re-entering the information is not gonna solve this problem.. There seems to be a problem, possibly with some of the content you are entering.. There maybe be a particular character or symbol in some of the records that is causing trouble, and if that character is used in relation to one of the drugs then it is likely to be used in relation to a number of the drugs.. i.e. you will have to replace it in ALL of these records..

Cherry_Cola · 10-03-2011 01:23AM

Thanks, I've contacted you there. The only symbols used that I can see causing a problem are the < and > signs.

Cherry_Cola · 14-03-2011 10:33AM

Finally managed to get it working

I re-entered all data into a table and used the table as the row source for the combo box and now all values are showing in the combo box!

Now I just have to figure out how to produce a new combo box based on a selection made in a previous combo box!

JonathanAnon · 14-03-2011 05:59PM

sorry I meant to respond.. I couldnt get any of those databases open.. tried a few of the conversion tutorials on youtube and nothing worked..

Cherry_Cola · 14-03-2011 06:47PM

JonathanAnon wrote: »

sorry I meant to respond.. I couldnt get any of those databases open.. tried a few of the conversion tutorials on youtube and nothing worked..

It's no problem, thanks very much for the help.

I may be back to you in regards to synchronising combo boxes but I'll have a crack at it first and see how I get on.

TommyQ · 14-03-2011 09:21PM

Hey Cherry,

Just noticed this thread. Could you post the database as you have it now and i'll have a look at whats going wrong. I could post my suggestions but it would be a lot easier if I had the data to work with.

Cheers,
Laoistom

Microsoft Access 2007 Help

Comments