Error 3314 with mistyping in combo box

[Dhalsim]

Hello

I'm working on a database for post mortem records. I've cobbled together a functioning database that does mostly what I want it to. It's not particularly relational, just one giant spread sheet, but the main feature is a form used to restrict how species data is entered.

I have a 'species' table (with species names, latin names, species codes) which I'm using as a lookup table, with several combo boxes autofilling after selection in one of the comboboxes from this table. I only want users to select from the list in the table so I have 'limit to list' on, but also want them to be able to type in the box and the list predict the species. I have this working but the problem is if you type a word which is not in the list I get error 3314, because I have 'required field' as true I believe.

Can anyone help me get around this problem? Some the code for the comboboxes is below. Many thanks in advance.

Private Sub Cmb_LATIN_Change()
Me.Cmb_WWTSPP.Value = Me.Cmb_LATIN.Column(1)
Me.txt_FAMILY.Value = Me.Cmb_LATIN.Column(7)
Me.Cmb_PSLATIN.Value = Me.Cmb_LATIN.Column(5)
Me.Cmb_SPPNO.Value = Me.Cmb_LATIN.Column(3)
Me.Cmb_PSSPP.Value = Me.Cmb_LATIN.Column(4)
Me.txt_uniquesppid.Value = Me.Cmb_LATIN.Column(0)
Me.Cmb_PSSPPNO.Value = Me.Cmb_LATIN.Column(6)
End Sub
Private Sub Cmb_PSLATIN_Change()
Me.txt_FAMILY.Value = Me.Cmb_PSLATIN.Column(7)
Me.Cmb_SPPNO.Value = Me.Cmb_PSLATIN.Column(3)
Me.Cmb_WWTSPP.Value = Me.Cmb_PSLATIN.Column(1)
Me.Cmb_LATIN.Value = Me.Cmb_PSLATIN.Column(2)
Me.Cmb_PSSPP.Value = Me.Cmb_PSLATIN.Column(4)
Me.txt_uniquesppid.Value = Me.Cmb_PSLATIN.Column(0)
Me.Cmb_PSSPPNO.Value = Me.Cmb_PSLATIN.Column(6)
End Sub
Private Sub Cmb_PSSPP_Change()
Me.txt_uniquesppid.Value = Me.Cmb_PSSPP.Column(0)
Me.Cmb_LATIN.Value = Me.Cmb_PSSPP.Column(2)
Me.Cmb_WWTSPP.Value = Me.Cmb_PSSPP.Column(1)
Me.txt_FAMILY.Value = Me.Cmb_PSSPP.Column(7)
Me.Cmb_PSLATIN.Value = Me.Cmb_PSSPP.Column(5)
Me.Cmb_SPPNO.Value = Me.Cmb_PSSPP.Column(3)
Me.Cmb_PSSPPNO.Value = Me.Cmb_PSSPP.Column(6)
End Sub
Private Sub Cmb_PSSPPNO_Change()
Me.Cmb_LATIN.Value = Me.Cmb_PSSPPNO.Column(2)
Me.txt_FAMILY.Value = Me.Cmb_PSSPPNO.Column(7)
Me.Cmb_PSLATIN.Value = Me.Cmb_PSSPPNO.Column(5)
Me.Cmb_WWTSPP.Value = Me.Cmb_PSSPPNO.Column(1)
Me.Cmb_PSSPP.Value = Me.Cmb_PSSPPNO.Column(4)
Me.txt_uniquesppid.Value = Me.Cmb_PSSPPNO.Column(0)
Me.Cmb_SPPNO.Value = Me.Cmb_PSSPPNO.Column(3)
End Sub
Private Sub Cmb_SPPNO_Change()
Me.Cmb_LATIN.Value = Me.Cmb_SPPNO.Column(2)
Me.txt_FAMILY.Value = Me.Cmb_SPPNO.Column(7)
Me.Cmb_PSLATIN.Value = Me.Cmb_SPPNO.Column(5)
Me.Cmb_WWTSPP.Value = Me.Cmb_SPPNO.Column(1)
Me.Cmb_PSSPP.Value = Me.Cmb_SPPNO.Column(4)
Me.txt_uniquesppid.Value = Me.Cmb_SPPNO.Column(0)
Me.Cmb_PSSPPNO.Value = Me.Cmb_SPPNO.Column(6)
End Sub
 
Private Sub Cmb_WWTSPP_Change()
Me.txt_uniquesppid.Value = Me.Cmb_WWTSPP.Column(0)
Me.Cmb_LATIN.Value = Me.Cmb_WWTSPP.Column(2)
Me.Cmb_PSSPP.Value = Me.Cmb_WWTSPP.Column(4)
Me.txt_FAMILY.Value = Me.Cmb_WWTSPP.Column(7)
Me.Cmb_PSLATIN.Value = Me.Cmb_WWTSPP.Column(5)
Me.Cmb_SPPNO.Value = Me.Cmb_WWTSPP.Column(3)
Me.Cmb_PSSPPNO.Value = Me.Cmb_WWTSPP.Column(6)
End Sub

 

[EternalMyrtle]

No offense, but this seems like a very odd way to do this.

Perhaps work on making the database more of a relational database and less of a big spreadsheet. If you work on the data structure, I bet you will find there is an easier way to present your information without relying on so much code to set the values.

For example, if you have a table just for species data, whenever you select a certain species that information will be automatically associated with it through a relationship. You won't have to rely on code set all the values like you are currently doing because the data will be linked through your relationship.

I hope this makes sense.

 

[Dhalsim]

Hello, none taken, thanks for the reply. No that does makes sense. So for the species table (see attached) that would involve having 7 tables instead of 1 with unique spp id as the key and linked via relationships. Then having 7 combo boxes with each of the tables as row source? How would I link the combo boxes so they all update on the following selection in one of the boxes? Thanks.

 

[EternalMyrtle]

Sorry I disappeared: I went away for a long weekend. I will take a look at this and get back to you. You probably just need a simple query to present the data the way you want to present it.

 

[EternalMyrtle]

This is an excel table I thought you were posting your database...

Make sure your field names do not have spaces in them. I noticed there were spaces in the Excel sheet.

Family should be its own table: put all the family names in there and put FamilyID as the FK in your species names table. You may already have it that way but I am not sure. If there are only a small number of values you could use a value list instead.

I need to see the database to help you.

 

[Dhalsim]

Hello again sorry I've been away for a while also and not been able to give this attention.

Sorry the table was just to demonstrate the table in the db. And sorry I don't feel I can post the whole db because the data is not mine to share.

I appreciate the set up might not be perfect but everything is working with it I just need to find a way to prevent users only selecting items in the combo dropdown list
but allowing the ability to use the keyboard to select first letters of the entry, yet preventing keystrokes such , . / etc. Any ideas?

Many thanks.