Data Type Mismatch!

Hi all!

I'm getting a Data Type Mismatch Run-time Error, and I can't seem to figure out what's mismatched where...

Basically, what I'm trying to do is to determine if a current record exists based on specific combinations of 4 fields on a form. If the record doesn't exist, it prompts the user, and I've gotten that done. However, when the record exists, I'm getting a data type mismatch error. Could someone please help me out here?

Data for testing:
Man Year Certificate No: 0212022
Prior Approval Date: 25/02/2003
Type of Prior Approval: New
Work Permit Length:1 year

The form in question is frmMYE and the table in question is tblMYE

Looking forward to your assistance!

~Sharon~

In code you don't put single quotes around datavariables (dates are not text, the are a number)

Change you line of code to

Set DAOrs = DAOdb.OpenRecordset("SELECT * FROM tblMYE " & _
"WHERE tblMYE.MYECert='" & Me.txtMYECert & "' And tblMYE.PADate=" & Me.txtPADate & " " & _
"And tblMYE.PAType='" & Me.cboPAType & "' And tblMYE.WPLength='" & Me.cboWPLength & "'")



S-

sbaxter,

Thanks again for that clarification!

However, even though all the details are already in the database, I still get a prompt that tells me that the information is not in the database. As a result, I get double entries. What am I doing wrong? Am I missing something?

Thanks again!

Walk me through your steps on how you want it to work (as far as data entry), so I know what you are doing.

Also you really should add a PK to your table (will stop you from adding duplicates


S-

Hi again!

Firstly, the reason that I can't add a primary key is because values may be duplicated. For example:

Instance 1:

Man Year Cert No: 0212022
Date of Issue: 25/02/2003
Type of PA: New
Length of WP: 1 year

Instance 2:

Man Year Cert No: 0212022
Date of Issue: 25/02/2003
Type of PA: Transfer
Length of WP: 2 years

Instance 3:

Man Year Cert No: 0212022
Date of Issue: 25/02/2003
Type of PA: New
Length of WP: 2 years

Would it be possible for me to set a primary key based on 4 fields?

Step By Step for Data Entry

1. User to key in 1st 4 fields, up to the Length of WP
2. If the same record exists in the database for the 4 fields, to prompt the user that the same record already exists and ask whether want to make amendments. If yes, display all fields, if no, message box to prompt to re-enter details and the form is cleared for data entry.
3. If the record doesn't exist, to prompt if the user would like to add a new record. If yes, to set focus on Number of MYE. If no, prompts the user to re-enter details and the form is cleared.

Really appreciate your time and help!

~Sharon~

Would it be possible for me to set a primary key based on 4 fields?

As many as you want, but I would recommend going past four or five (gets harder to manage at that point)

Send a PM to supersubra to look at you DB, everytime I run it I get a "unhandled exception error" and cant get it to work


S-

Just when building criteria within form format the date to mm/dd/yy. Now it works. There was some error in DAO object library reference which caused that exception error. I recreated a new db and imported all the tables and forms and it started working. I have made some cosmetic changes like putting the primary key (4 fields) in the form header and accept buttons in the form footer area. In unbound forms there is no need for recordselector navigation buttons so I switched them off. As sbaxter says it will be unwildy to handle primarykeys more than 4 fields.

I have not modified the accept area code.

bye

supersubra & sbaxter,

Thanks a lot!!!

Very very grateful for your assistance!

~Sharon~

Supersubra,

Am still having trouble with loading the Project Description for some reason.

I'd appreciate it if you could take a look at my db again.

Also, I'm still continuously getting Type Mismatch in all my forms, even though I'm using the exact code which I used for 2 previous forms which are working perfectly... Any suggestions?

Thanks a lot!

~Sharon~

Yes the behaviour is erratic some times it gets updated some times it dont. I will check out on sunday indepth and let u know.

I dont get type mismatch anywhere can u tell me where u get.

Hi Supersubra!

Thanks a lot for your time! I'll be very grateful if you could advise.

My type mismatch is in another db. I also get a runtime error in Frm Discipline and my Date of occurence gets highlighted. Do I have to put the # symbol somewhere?

I'm attaching another db as well.

Am really looking forward to your advice.

Thanks heaps

~Sharon~