Split Form to Display [Open] Instead of the AutoID

[steve21nj]

Without the use of a combo box, is there a way to change the text box's name to display [Open] instead of the autoID number access assigns?

For example instead of:

Open LastName FirstName
1 Smith John
2 Jane Sara

It would display:

Open LastName FirstName
Open Smith John
Open Jane Sara

 

[boblarson]

Sure, if you set the control source to:

=IIf(IsNumeric([Open]), "Open", "")

 

[steve21nj]

I have a double click event associated with the ID.

Private Sub RequisitionID_DblClick(Cancel As Integer)
    DoCmd.OpenForm "ReviewPurchaseOrder", , , "RequisitionID=" & RequisitionID
End Sub

If I change my control source, it produces an error because of the statement. How can I change the above coding to fit your statement?

 

[boblarson]

Put a second text box under the first and bind it to the ID field. Then in the double click event refer to that one instead (and by the way you should use

Me.RequisitionID

instead of just by itself, even thought it normally works sometimes Access gets confused so it is better to be specific.

So, if you put the second text box on there and called it txtReqID you would use this code:

Private Sub RequisitionID_DblClick(Cancel As Integer)
    DoCmd.OpenForm "ReviewPurchaseOrder", , , "RequisitionID=" & Me.txtReqID
End Sub

 

[steve21nj]

Bob,
I haven't had any luck with the above code. I keep getting a run-time error '2447'.

I am attaching a few screen shots:

• the error screen shot
• the #Name? on txtReqID when it is displayed in the datasheet of the split form (it doesn't display open)
• *i'm not 100% what you mean by binding it to RequisitionID because it goes into a datasheet*
• the line error VB
• and the text properties of the txtReqID

When I would use Me.RequisitionID in the code, it would generate another error. Any thoughts?

 

[boblarson]

the error screen shot
the #Name? on txtReqID when it is displayed in the datasheet of the split form (it doesn't display open) Can you show what you currently have as the control source?
*i'm not 100% what you mean by binding it to RequisitionID because it goes into a datasheet* Ignore because I couldn't see what you had at the time.

If you can, perhaps uploading a copy of the database would be helpful. But the first thing to check, because I just had this issue with someone else, is to go to the VBA window and to DEBUG > PROJECT and, if it shows you anything other than this line of code, fix it and run again. Keep doing that until you have no more errors or this is the only one. Sometimes the error does not tell the right story and, as in my other thread where the user was getting a macro not found error but it was actually due to a Word reference not being set, it can confuse things.

And one more thing - does RequisitionID actually exist in the ReviewPurchaseOrder Form's record source?

Sometimes it can help resolve some issues is to import (not copy and paste, but import) everything into a brand new database file.

 

[steve21nj]

Can you show what you currently have as the control source?

=IIf(IsNumeric([Open]),"Open","")

Uploaded a partical copy of what I have [sample1] with a few screen shots[sample2]. I went back to the original code in the DblClick:

Private Sub RequisitionID_DblClick(Cancel As Integer)
    DoCmd.OpenForm "ReviewPurchaseOrder", , , "RequisitionID=" & RequisitionID
End Sub

VBA window and to DEBUG > PROJECT and...

I attached a screen shot of an outlook error that i've been getting from day one that "shouldn't" effect it.

And one more thing - does RequisitionID actually exist in the ReviewPurchaseOrder Form's record source?

Yes, it is the first column in my query and on the form page.

Thank you again!

 

[boblarson]

First thing that jumps out at me is that there is no field named [OPEN] in your form's record source. That is kind of an important detail.

Second, fix the Outlook error by either adding the reference or by changing the items to OBJECT instead of Outlook.Application, Outlook.MailItem, etc. Code that has issues like that can make other things not work and the errors that pop up are really not telling the correct story.

And last, the correct formula based on your original post should be:

=IIf(IsNumeric([RequisitionID]),"Open","")

 

[steve21nj]

Made the changes, works like a charm! Thank you!

We can't give thanks in the post anymore? What happened to the thumbs???

 

[boblarson]

Made the changes, works like a charm! Thank you!

We can't give thanks in the post anymore? What happened to the thumbs???

Jon did some update to the site. So, there may be some things he has to fix. I'll mention that to him. I already sent him a PM about when you click on the Top Posters link that it takes you to the emoticons page instead.