Open form using Input box value (1 Viewer)

[alex_]

Hello.

I have a form in my db (call it MAIN) containing various fields from table tbl_main. What I want to do, is to create a button on my switchboard in order for the user to open the form in read only mode, based on a value for “net no” that he/she will give as an input. This value corresponds to NET field in table (tbl_main).

I thought that by using the docmd.openform with open arguments will do the job, however I didn’t make it to work. The code:

Private Sub option2_view_Click()
On Error GoTo Err_RecViewUser_Click

Dim asknet As String

asknet = InputBox("Please Enter a valid Net No", "NET No REQUIRED")

If asknet = "" Then
MsgBox "Enter Net No to continue", vbInformation, " NET No VALUE NEEDED"
Else
DoCmd.OpenForm " MAIN", acNormal, , , acFormReadOnly, acWindowNormal, {enter your suggestion for Openargs here}

Forms! MAIN.NavigationButtons = False

Exit_RecViewUser_Click:
Exit Sub

Err_RecViewUser_Click:
MsgBox Err.Description
End If
End Sub

I have also gave a try with the where option of the openform, but without any luck :banghead:

Thank you in advance for your assistance. Any suggestions are more than welcome.

Alex

 

[pr2-eugin]

Hello Alex, the syntax for DoCmd.OpenForm is not correct in your case. The open args is used for sending some value into the form, which you can use at a later stage.. But if you want ot open the Form based on the value already existing you have use the 'WhereCondition' option.. Check the syntax..

 DoCmd.OpenForm FormName, View, FilterName, [I][B][COLOR=Red]WhereCondition[/COLOR][/B][/I], DataMode, WindowMode, OpenArgs

The higlighted place is where your condition should go.. so your syntax will be something like

DoCmd.OpenForm "MAIN", , , "NET="&asknet, acFormReadOnly

 

[alex_]

Thank you very much for your reply. I knew there was something wrong with the openargs, thus my last attempt with the where condition.

Anyway I've used your suggestion and received the following error message:

"Data type mismatch in criteria expression"

Just for your information the net string entered in the inputbox is always number (i.e something like 1000125) and the corresponding field (NET) has been defined as text.

Thank you for your support.

Alex

 

[pr2-eugin]

Hello Alex if the field has been defined as a Text in the Table does not matter if you enter number.. to access a string you have to cover the value you pass through as a string.. So.. your code becomes..

DoCmd.OpenForm "MAIN", , , "NET='" & asknet & "'", acFormReadOnly

Just a friendly advice, selecting text value is not a great filter.. Because there might be several 'Paul' in the DB, but this will return ONLY the first match form the DB..
But if they are unique thats fine, if not you should think of selecting some reliable field as a filter.. maybe the ID, best to use primary key..

 

[alex_]

That did the work sir! I understand that although I know how to do something, I am having some difficulties with the systax of string values. I guess, I have to practice some more.

Thank you for your advice for the fliter. The value is unique, so that should not pose a problem. However a good advice is always acceptable.

Kind regards,

Alex

 

[pr2-eugin]

You are welcome, you will get the hang of things when you work on this more..

 

[alex_]

Just a quick question about the same problem:

Is it possible instead of using the input box to enter a value, to have everything in the open form command using something similar to the Like[enter net no] query criterion in the where option of the command?

Thank you,

Alex.

 

[pr2-eugin]

....to have everything in the open form command using something similar to the Like[enter net no] query criterion in the where option of the command?.....

Am sorry I so not get what you are trying to say.. Can you explain what the current open form is? what query criteria??

 

[alex_]

Sorry for troubling you with my latest question. I wrote it a little bit fast, because I had to leave my office... Anyway I was referring to query design, where you can enter in certain or all fields of a selected table the "like" operator in the criteria of a query to use a screen request for the value of a specific form field (or fields) before opening the form itself. For example, if I use the like operator in a query for my table tbl_main and its field NET, I'll end up with the SQL command:

WHERE (((tbl_main.NET) Like [Enter NET Number]))

The question is, can I use the above (or similar) in the "where" option of the open form command to avoid asking for a value using the input box (as I did in the original example).

Of course one might just say that you can simply create the query with the like operator and have this screen showing every time you open the form. But I was wondering if it is possible to do it with the docmd.openform.

I hope it is clearer now.

Thank's once more,

Alex

 

[pr2-eugin]

You CAN create a query to do it, but also you can use Like operator in the where condition of the DoCmd.OpenForm, which brings me again to the question, what are you trying to do? Because in my experience you use LIKE only when you are not sure of how the name looks like.. Maybe 'Paula / Pauline / Paul'.. But if the numbers are Unique then why should you use LIKE? it takes up a bit of computational space. also the result returned will not be accurate..

OR

Are you trying to make the BIG input box not appear; but show a small box that takes the value, as it does for when you run a Query? If so you have to Create a built in Query and say name it returnData and use that as the 'filter'.. something like..

DoCmd.OpenForm "MAIN", , returnData, , acFormReadOnly

This should prompt you to enter the value.. before opening the form..

 

[alex_]

Dear pr2-eugin, thank you very much for your kind advice. Yes you are perfectly right with your last comment. It is clear for me that it will be better to use a query with a like operator instead of the input box, since the NET number that has to be entered is 10 digits long (not my idea!) and it will be difficult for the user to remember it, or type it without making a mistake. So, although it is unique and -as correctly mentioned- there is no need for LIKE, I'll prefer it for the reason mentioned before.

Once more I would like to thank you for your kind effort to explain things. I hope that this thread was useful for other (novice?) viewers as it was for myself.

Regards,

Alex

 

[Rachel Leimg]

Though this is an old post, it helps to solve my problem too. Thank you so much.

 

[Mikesss]

Hello Alex if the field has been defined as a Text in the Table does not matter if you enter number.. to access a string you have to cover the value you pass through as a string.. So.. your code becomes..

DoCmd.OpenForm "MAIN", , , "NET='" & asknet & "'", acFormReadOnly

Just a friendly advice, selecting text value is not a great filter.. Because there might be several 'Paul' in the DB, but this will return ONLY the first match form the DB..
But if they are unique thats fine, if not you should think of selecting some reliable field as a filter.. maybe the ID, best to use primary key..

That worked very well!! Thank you!