Issues attempting to open a form to a specific record

[rth@trinitylabs.org]

I have a database that uses a report to show all open jobs, the idea is to click on a job and have a form open so the user can edit that order. All the work is done in the Event 'On Click' where the OrderNumber is passed to the form so it knows which record to open. Unfortunately this doesn't work as expected. When the Order Number is an actual number, the Error "Data Type Mismatch in Criteria Expression" is received. When the Order Number is text, a message box pops up, requesting 'Enter Parameter Value?'

The Order Table tblOrders, has Primary Key: OrderNumber (Short Text); OrderDate, StartDate, CompletionDate, ActualStartDate, ActualCompletionDate, CreatedOn (Date/Time); fkCompanyID (Long Int); AgentName, Title, AWB, CreatedBy (Short Text); Description (Long Text); Closed (Boolean).

The Company Table tblCompany has Primary Key: CompanyID (Autonum); CompanyName, Address1, Address2, City, State, PostalCode, County, CreatedBy (Short Text); Notes (Long Text); Active (Boolean); CreatedOn (Date/Time).

tblContacts has Primary Key: ContactID (Autonum); AgentName, fkOrderNumber, Notes, CreatedBy (Short Text), CreatedOn (Date/Time).

tblEmail & tblTelphone are similar, Primary Key is eMail/TelephoneNumber (Short Text) & fkCompanyID (Long Int).

Most of the functions have not been implemented since I ran into this issue of "Data Type Mismatch" almost out of the gate as it where. I've attached a working database to this post, with a Navigation Form as the front porch. Clicking the Open Orders Button will bring up the Report mentioned above, than clicking on any of the entries in the Report itself will repeat the error noted above. I am suspicious using a Short Text as Primary Key might have something to do with it. But before I revamp the whole idea, thought I'd ask.

Thanks all. Have a good day.

 

[plog]

I have a database that uses a report to show all open jobs, the idea is to click on a job and have a form open so the user can edit that order.

1. No synonyms. Nowhere in your database do you have a table or field called anything like 'jobs'. We aren't familiar with your organization or your database. What is in it is all we know.

2. You shouldn't jump from a report to a form. Instead, make a form to show the data and add a button to jump to that other form. More intuitive for users, more options to facilitate interaction for user as well.

When the Order Number is an actual number, the Error "Data Type Mismatch in Criteria Expression" is received. When the Order Number is text, a message box pops up, requesting 'Enter Parameter Value?'

3. The Order Number is never an actual number. OrderNumber is in the database as Short Text, it is always text. Even if it is in the database as "208", it's still text and must always be treated as text.

4. Not only do you have to treat it as text, you must make sure it matches exactly what is in the database if you want to match on it. In your DoCmd.OpenForm call you don't just pass OrderNumber, you clean it up first by putting it inside Trim(). Don't do that, because to a computer the string "ABC123" is not the same as " ABC123". It won't find a match.

5. This is the correct way to make your DoCmd:

DoCmd.OpenForm "frmEditOrders", acNormal, "", "[OrderNumber]='" & Me.txtOrderNumber & "'"

 

[rth@trinitylabs.org]

1. No synonyms. Nowhere in your database do you have a table or field called anything like 'jobs'. We aren't familiar with your organization or your database. What is in it is all we know.

True, tblOrders is what you are looking for. In my environment Jobs isn't used.

2. You shouldn't jump from a report to a form. Instead, make a form to show the data and add a button to jump to that other form. More intuitive for users, more options to facilitate interaction for user as well.

Not sure how clicking on an item in a list is not that intuitive?
Reports do lists well, and since there is an 'On Click' Event, thought it better then a button. But I can see where a continuous form would do pretty much the same thing, though maybe not as pretty. But I still wish to stay clear of buttons in the list, as it really clutters things.

3. The Order Number is never an actual number. OrderNumber is in the database as Short Text, it is always text. Even if it is in the database as "208", it's still text and must always be treated as text.

Yes I understand that, and it is the first thing I checked when I got this error.

4. Not only do you have to treat it as text, you must make sure it matches exactly what is in the database if you want to match on it. In your DoCmd.OpenForm call you don't just pass OrderNumber, you clean it up first by putting it inside Trim(). Don't do that, because to a computer the string "ABC123" is not the same as " ABC123". It won't find a match.

I also understand that. Which is what has me confused. Since I'm thinking of it as text, and attempted to pass it as text in several ways -- see the rem'd statements above the last DoCmd.

5. This is the correct way to make your DoCmd:

DoCmd.OpenForm "frmEditOrders", acNormal, "", "[OrderNumber]='" & Me.txtOrderNumber & "'"

I attempted that. I also attempted:

DoCmd.OpenForm "frmEditOrders", acNormal, "", "[OrderNumber]=" & (me.txtOrderNumber & "")

and

DoCmd.OpenForm "frmEditOrders", acNormal, "", "[OrderNumber]=" & Str(me.txtOrderNumber & "")

and

DoCmd.OpenForm "frmEditOrders", acNormal, "", "[OrderNumber]=" & Str(me.txtOrderNumber)

and

DoCmd.OpenForm "frmEditOrders", acNormal, "", "[OrderNumber]=" & Trim(Str(me.txtOrderNumber))

Additionally I tried all the iterations with the '& ""' as a preamble, vis trailing, again all No Joy. The Active DoCmd at the bottom of the routine was my first attempt and what I went back to, so you could see what I had initially tried. The rem'd out statements where also attempted, but I over wrote some during testing.

Second, I noted in the OP that when OrderNumber was an actual number, the database throws the "Data mismatch" error. But when OrderNumber is a text (ie 'T123') a msgbox pops up requesting "Enter Parameter Value?" -- what I didn't note is if one enters the Order Number clicked on (an actual text value), then everything works as expected. Which is what made me suspicious there was a problem using a Short Text value as Primary Key. And is why I had the Debug.Print statement, as my second thought is that Me.txtOrderNumber.Value is NULL (for what ever reason). What I haven't tried is entering any other OrderNumber and seeing what I get. And just checked, entering any OrderNumber will open the form to the OrderNumber Entered. So now suspecting the Report is passing a number (maybe NULL) if the OderNumber is an actual number and a blank when the OrderNumber is any form of text.

 

[KitaYama]

5. This is the correct way to make your DoCmd:

DoCmd.OpenForm "frmEditOrders", acNormal, "", "[OrderNumber]='" & Me.txtOrderNumber & "'"

I attempted that. I also attempted:

None of your remarks is what @plog suggested. Pay close attention to single and double quotes.
If not sure copy & paste.

 

[plog]

I opened your database, ran your report, clicked on an order, got the error, clicked debug, modified the code to what I posted above, saved, exited, reopened, reclicked and went to your order form just like you want.

The code I posted above is the working code I used in your database. Copy from above and paste it where your docmd is and it will work.

 

[rth@trinitylabs.org]

I opened your database, ran your report, clicked on an order, got the error, clicked debug, modified the code to what I posted above, saved, exited, reopened, reclicked and went to your order form just like you want.

The code I posted above is the working code I used in your database. Copy from above and paste it where your docmd is and it will work.

Oh. I get it now. Thanks. I didn't see the double/single quotes until KitaYama pointed it out to me. So thanks to you for the initial post; and to KitaYama for pointing out what I overlooked.

 

[rth@trinitylabs.org]

None of your remarks is what @plog suggested. Pay close attention to single and double quotes.
If not sure copy & paste.

Ah, thank you, missed that.