Option group problem

[Mansoor Ahmad]

Dear All

I have got following objects on a form
An option group with 2 options (Option 1. Part No, Option 2. Ref No)
A command button to open a report based on a query

What I want?

I chose an option, say option for Part No. Now when I click on command button to open the report based on a query, I want Part No field to be set as a parameter for that query. So that after clicking on command button it would prompt for the Part no. Similarly if I chose option 2 then on clicking the command button it prompts for Ref No.

I hope I am able to explain my problem. Looking forward to reply.

 

[namliam]

inside the button:

Dim myInt as double
If OptionGroup =1 then
    myint = InputBox("Give Part number", "GIVE!")
    DoCmd.OpenReport "", , , "[Partnumber] = " & myint
else
    myint = InputBox("Give Ref number", "GIVE!")
    DoCmd.OpenReport "", , , "[Refnumber] = " & myint
endif

As easy as 1,2,3 if you know how...

Regards

The Mailman

P.S. I hope your fieldnames are NOT "Part no" and "Ref no" !

 

[Mansoor Ahmad]

Mailman

Thank you very much for your quick reply. I will try it out and let you know but it seems to be something that I wanted.

Thank you once again.

 

[Mansoor Ahmad]

Namliam

Thank you for your reply earlier.

I have tried it and it worked but still some problems. I am using following code in a command button.


Private Sub Command51_Click()

Dim myInt As String


If optiongroup = 1 Then

myInt = InputBox("GIVE CUSTOMER", "GIVE!")

DoCmd.OpenReport "Report3", acViewPreview, , "[CUSTOMER] = " & myInt

Else
myInt = InputBox("GIVE PART NO", "GIVE!")
DoCmd.OpenReport "Report3", acViewPreview, , "[PART NO] = " & myInt
End If
End Sub


I am having two problems

1. When I click on command button, it prompts twice for the same option.
2. It does pulls data for option 1 but nothing happening even when I put in required values in prompted dialogue box that appears twice. Sometimes it also says mismatched data type.

I presume [CUSTOMER] and [PART NO] are the field names in underlying tables in the query to which Report3 is based on.

Thank you for you help again.

Looking forward to your reply

 

[namliam]

I notice you changed Dim myInt as double to String....

THus your mismatch...

You were talking numbers now String ... Which is it?! Get your facts ... Geez..
Use below for strings
DoCmd.OpenReport "Report3", acViewPreview, , "[CUSTOMER] = '" & myInt & "'"

I presume [CUSTOMER] and [PART NO]

Duh ! Yes, sorry its been a long day...

Regards

 

[Mansoor Ahmad]

Namliam

Thank you very much for the reply and sorry about the confusion I caused earlier on. Actually I had to add another option in the group.

I am in a very early learning stage of VB codes. When I used your original code it gave message for mismatched data type, that is why I tried to use my brain and changed 'double' to 'string' but obviously I did not know that something else needed changing as well.

Now Option 1 [CUSTOMER] is Text, option 2 [PART NO] is Number and option 3 [Ref No] is also Number. Using the corrected code below only for two options for now (I hope I would manage with the third option later on), Clicking on command button for option 1 pulls the data correctly first time but for option 2 (Number format) it does open the report but does not retrieve any data.

To my little understanding String does support both text and number values. I may be wrong.


Dim myInt As String

If frOverallqueries = 1 Then

myInt = InputBox("GIVE CUSTOMER", "GIVE!")

DoCmd.OpenReport "Report3", acViewPreview, , "[CUSTOMER] = '" & myInt & "'"

Else
myInt = InputBox("PART NO", "GIVE!")
DoCmd.OpenReport "Report3", acViewPreview, , "[PART NO] = '" & myInt & "'"
End If


Thanks for your help again. Looking forward to your reply.

 

[namliam]

For using more than 2 Select case is better than If
Select case frOverallqueries
case 1
... more code
case 2
... more code
case 3
... more code
case else
... more code
end select

Or...
If frOverallqueries = 1 then
... code
elseif frOverallqueries = 2 then
... code
elseif frOverallqueries = 3 then
... code
else
... code
endif

Or...
If frOverallqueries = 1 then
... code
else
if frOverallqueries = 2 then
... code
else
if frOverallqueries = 3 then
... code
else
... code
endif
endif
endif

I am sure you can see the advantages...

To my little understanding String does support both text and number values. I may be wrong.

Sorry wrong! String = string = text <> Number ! Strings can hold numbers but that is different from beeing a number... If you by 'support' mean Hold/store then you are right
In the table(not the option group):
Customer (i am guessing) is a Customer name (text)
Part NO (DONT USE SPACES !) is a Part number (number)
If above 2 are correct assumptions then use the "= '" & myInt & "'" notation for the customer and use the "= " & myint notation for part no.
The change as you can see is dependant on the field in the table rather than the input source...

Regards

 

[Mansoor Ahmad]

Namliam

Thanks again for your reply.

Thank you for explaining in detail. I hope I have got things straight in my mind now with your help.

I really appreciate your help. Thanks

 

[namliam]

if only you could get my name right in your head.... *sigh*

Regards

 

[Mansoor Ahmad]

I am really sorry for that.

I only did the mistank once in first reply and realized afterwards. I thought probaly I have got away with it but obviously not. My mind was probably stuck in VB code.

I apologise for my mistake.

 

[namliam]

No Biggy Happy to help

 

[Mansoor Ahmad]

Namliam

Its me again. Hopefully last time. A little more help can solve my problem.

I am using the following code now as you advised.

For Number fileds [Part No] and [Ref No], I have used

'Dim myint1 As Double' and the notation changed to "="&myint1

I have also tried

''Dim myInt As String' and notation "="&myint in both Case 2 and 3

but in both cases, Case 1 works fine but Case2 and 3 does open the report but do not pull any data.


Private Sub Command51_Click()

Dim myInt As String
Dim myInt1 As Double

Select Case frOverallqueries

Case 1
myInt = InputBox("GIVE CUSTOMER", "GIVE!")
DoCmd.OpenReport "Report3", acViewPreview, , "[CUSTOMER] = '" & myInt & "'"

Case 2
myInt1 = InputBox("GIVE PART NO", "GIVE!")
DoCmd.OpenReport "Report3", acViewPreview, , "[PART NO] = " = " & myInt1"

Case 3
myInt1 = InputBox("GIVE Ref No", "GIVE!")
DoCmd.OpenReport "Report3", acViewPreview, , "[Ref No] = " = " & myInt1"
End Select
End Sub

Looking forward to your reply. Thanks

 

[namliam]

Hmz, I thought i could take a short cut... but you proved me wrong

DoCmd.OpenReport "Report3", acViewPreview, , "[Ref No] = " = " & myInt1"

Is not what i meant....

If above 2 are correct assumptions then use the "= '" & myInt & "'" notation for the customer and use the "= " & myint notation for part no.

How come taking into account above you get
DoCmd.OpenReport "Report3", acViewPreview, , "[Ref No] = " = " & myInt1"
and
DoCmd.OpenReport "Report3", acViewPreview, , "[CUSTOMER] = '" & myInt & "'"
?? ??
Maybe you see your problem now?? For your ease I will give it to you
DoCmd.OpenReport "Report3", acViewPreview, , "[Ref No] = " & myInt1
Also there is no need to do seperate dims and seperate variables... but if you prefer, do so...

Regards

 

[Mansoor Ahmad]

I am sorry to be back. Thank you for your patience.

Am I stupid or what? I think it would be frustrating for you as well.

I have done exactly the same way as you said. I did not use other variable or dim. I used the same variable

Dim myInt As String.

My statement is

DoCmd.OpenReport "Report3", acViewPreview, , "[Ref No] = " & myInt

and it prompted as

'data type mismatch in criteria expression'

Any suggestion please.

While I am here what if a field contains both text and numbers e.g. Ref No may contain text and numbers like FGD23.

 

[namliam]

Go to table design and look at the TYPE of [ref no] if that is text use the same as Customer....

Regards

-- A number field can ONLY contain numbers... anything else MUST be a text field --