Input box question

mdschuetz

mdschuetz

Nerd Incognito
Local time
Today, 15:32
Joined
Oct 31, 2007
Messages
49
I am trying to use an input box value as a parameter to return values, it is comming back blank. If I remove the input box related code, it returns all values, so there must be an error with the input box coding. Can someone help me identify it?

Code: 
Private Sub cmdQC9_Click()

[COLOR="Red"]Dim strINP As String
strINP = InputBox("Enter Invoice Number", "Invoice Number")[/COLOR]


Dim objXxL As Excel.Application
Dim objWxB As Excel.Workbook
Dim objWxS As Excel.Worksheet

Set objXxL = Excel.Application
Set objWxB = objXxL.Workbooks.Open("\\mypath.xls")
Set objWxS = objWxB.Worksheets("Form")

With objWxS
.Cells(6, 3).Value = Me.txtTD
.Cells(13, 13).Value = Me.txtYes
.Cells(13, 16).Value = Me.txtNo
.Cells(10, 4).Value = Me.PurchaseOrderNumber
.Cells(8, 3).Value = Me.cboSupplierID.Column(1)
.Cells(8, 19).Value = Me.cboAccount.Column(1)
.Cells(10, 9).Value = Me.cboPJO.Column(1)
.Cells(13, 31).Value = Me.cboInspect.Column(0)


Dim xx As Integer



xx = 18



 [Purchase Orders Subform].Form.Recordset.MoveFirst
 


While Not [Purchase Orders Subform].Form.Recordset.EOF
Dim strIDD As String
[COLOR="Red"]Dim strINV As String

strINV = Me.[Purchase Orders Subform].Controls("Invoice").Value[/COLOR]

strIDD = Me.[Purchase Orders Subform].Controls("UnitsReceived").Value

[COLOR="red"]If strINV = strINP & strIDD > 0 Then[/COLOR]


    .Cells(xx, 5).Value = [Purchase Orders Subform].Form!UnitsReceived

    .Cells(xx, 31).Value = [Purchase Orders Subform].Form!UnitsReceived

    .Cells(xx, 8).Value = [Purchase Orders Subform].Form!ItemDescription

    .Cells(xx, 26).Value = [Purchase Orders Subform].Form!PartNumber
 
    .Cells(xx, 6).Value = [Purchase Orders Subform].Form!txtUD
[COLOR="red"]End If[/COLOR]

    [Purchase Orders Subform].Form.Recordset.MoveNext

    xx = xx + 1

Wend




End With

objXxL.Visible = True





End Sub
 
Maybe I'm ignorant but i don't understand this statement
If strINV = strINP & strIDD > 0 Then


What is the > 0 ?

Brian
 
Brianwarnock said:
Maybe I'm ignorant but i don't understand this statement
If strINV = strINP & strIDD > 0 Then


What is the > 0 ?

Brian
Click to expand...

And, how can you evaluate a string to a number? You would need to do a conversion somewhere to compare the two.
 
Brianwarnock said:
Maybe I'm ignorant but i don't understand this statement
If strINV = strINP & strIDD > 0 Then


What is the > 0 ?

Brian
Click to expand...

I think he is checking that there is a positive value in strIDD. StrIDD holds the number of Units received so it seems reasonable.
 
But that is not what is happening, he should check that in a separate If .
What is happening is that ACCESS decodes the first bit and if TRUE returns -1 which is < 0 else if False, 0 which is = 0 thus no data.

Brian

BTW this subroutine looks like a replacement for the one we worked on in his previous thread.
 
Last edited:
Brian, You are correct. I was looking at what he wants to do. I think it should be something like

If strINV = strINP and cint(strIDD) > 0 Then

but perhaps he didn't want that.
 
LOL
I wondered if he wanted

If strINV = strINP & strIDD And strINV > 0 Then

but lets face it until he tells us we are shooting in the dark, so I will wait till he returns.

Brian
 
That was it, I used the
Code: 
&
symbol instead of
Code: 
And
and now, it works.

What is cint(strIDD) ?

what does the cint do?
 
Brianwarnock said:
LOL
I wondered if he wanted

If strINV = strINP & strIDD And strINV > 0 Then

but lets face it until he tells us we are shooting in the dark, so I will wait till he returns.

Brian
Click to expand...

If strINV = strINP & strIDD > 0 Then...

strINV is the invoice field in the subform.
strINP is the value in the input box.

strIDD > 0 is supposed to see if the value in strIDD is greater than the number zero, but from what I derived from your earlier post a string can't compare to a numerical value. Is that right? What is the proper way to do that part?
 
It was Bob Larson who stated that.
Cint converts a string to an integer.
I understand that you have the routine working now, that the error was a syntax error involving & and And.
I wondered why you defined number field as strings, I thought that it was inorder to concatenate them, hence the &.

Brian
 
This whole process of moving the data from access to excel is being difficult.

PHP: 
Date	Part Number	Description	Unit Price	PO ID:	GPC	Units Reqested	Units Received	Units Due	Invoice Number
11/9/2007	2252	     bolts	     $2.25	     27	  CAM	     5	             5	            0	     MOSL223224
11/9/2007	2452	     nuts	      $1.85	     27	  CAM	     10	            4	            6	     MOSL223224
11/9/2007	2241	     washers	   $0.96	     27	  CAM	     20	            12	           8	     MOSL223224
11/9/2007	2216	     springs	   $0.52	     27	  CAM	     50	            20	          30	     MOSL223224
11/9/2007	2171	     sprockets	 $1.95	     27	  CAM	     79	            59	          20	     MOSL223224
11/14/2007   2252	     bolts	     $2.25     	27	  CAM	     8	             1	            7	     MOSL223555


That is the subform I am pulling from. (datasheet)

It pulls the right data, however, its not putting it in its proper place.

I was thinking maybe using an ELSE statement in my While Loop to fix this.

Something like...

Code: 
Private Sub cmdQC9_Click()

Dim strINP As String
strINP = UCase(InputBox("Enter Invoice Number", "Invoice Number"))


Dim objXxL As Excel.Application
Dim objWxB As Excel.Workbook
Dim objWxS As Excel.Worksheet

Set objXxL = Excel.Application
Set objWxB = objXxL.Workbooks.Open("mypath.xls")
Set objWxS = objWxB.Worksheets("Form")

With objWxS
.Cells(6, 3).Value = Me.txtTD
.Cells(13, 13).Value = Me.txtYes
.Cells(13, 16).Value = Me.txtNo
.Cells(10, 4).Value = Me.PurchaseOrderNumber
.Cells(8, 3).Value = Me.cboSupplierID.Column(1)
.Cells(8, 19).Value = Me.cboAccount.Column(1)
.Cells(10, 9).Value = Me.cboPJO.Column(1)
.Cells(13, 31).Value = Me.cboInspect.Column(0)


    Dim xx As Integer
    Dim y As Integer
        xx = 18
        [Purchase Orders Subform].Form.Recordset.MoveFirst
        y = xx
    While Not [Purchase Orders Subform].Form.Recordset.EOF
        Dim strIDD As String
        Dim strINV As String
        
        strINV = Me.[Purchase Orders Subform].Controls("Invoice").Value
        
        strIDD = Me.[Purchase Orders Subform].Controls("UnitsReceived").Value
        
        
        If strINV = strINP And CInt(strIDD) > 0 Then
            If [Purchase Orders Subform].Form!UnitsReceived = "" And [Purchase Orders Subform].Form!UnitsReceived = "" _
            And [Purchase Orders Subform].Form!ItemDescription = "" And [Purchase Orders Subform].Form!PartNumber = "" _
            And [Purchase Orders Subform].Form!txtUD = "" Then
                y = y - 1
        
            Else
          
                .Cells(y, 5).Value = [Purchase Orders Subform].Form!UnitsReceived
            
                .Cells(y, 31).Value = [Purchase Orders Subform].Form!UnitsReceived
            
                .Cells(y, 8).Value = [Purchase Orders Subform].Form!ItemDescription
            
                .Cells(y, 26).Value = [Purchase Orders Subform].Form!PartNumber
             
                .Cells(y, 6).Value = [Purchase Orders Subform].Form!txtUD
                y = y + 1
            End If
        End If
        
            [Purchase Orders Subform].Form.Recordset.MoveNext
        
            xx = xx + 1
            y = y
    Wend
 
End With

objXxL.Visible = True

End Sub


Yep, that did it. Thank you guys for all the help. I'm very new to this and am learning as I go, this site is a godsend and your help is invaluable to helping people like me learn.

Thanks a million.

Marty
 
boblarson said:
Glad that between us all we could actually help. :)
Click to expand...


I do have a question about comparing string (text?) values to integers.
Is this the best way?

Code: 
Dim strSQL as String
strSQL = Me.[Purchase Orders Subform].Controls("UnitsReceived").Value

If CInt(strSQL) > 0 Then

.Cells(y, 5).Value = [Purchase Orders Subform].Form!UnitsReceived

End If

I want the code to actually compare numerical values, not return as a yes or no or true or false statement.
 
Yes, the conversion to a numeric value or using VAL(StringValueHere) would be necessary to compare values. You need to compare apples to apples; oranges to oranges, etc. So, the two should be the same datatype and if not, you should convert them to the same type for the comparison.

And, if one is numeric you should almost always compare numeric to numeric and not convert to string to compare against a string.
 
You must log in or register to reply here.

Similar threads

A
Solved checkbox placed in frame by a query, not responding
Replies
2
Views
565
Pat Hartman
P
S
change URL
Replies
3
Views
720
sonic8
S
Sam Summers
Inserting subform/Table data into specific fields in Excel
2
Replies
22
Views
2,382
Gasman
Gasman
Kitticus_Kattus
Cross Tab Query not accepting Form ComboBox as filter criteria
Replies
8
Views
914
CJ_London
C
C
Divide code to avoid "Procedure too large error"
Replies
18
Views
2,492
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom