If statement bad?

[mdschuetz]

Can anyone explain to me what I'm doing wrong with this?
I've been at this problem for over a week and its driving me mad. Nothing I try works.

I have a subform within a form.

This code lies in the main form on a command button cmdQC9 in an on_Click procedure.

If a value greater than 0 is present in the textbox on the subform I want to move cell data from both forms to an excel spreadsheet.

The subform is a datasheet.

The textbox UnitsReceived

Will someone please explain what I'm doing wrong?

Thanks,

Marty

Private Sub cmdQC9_Click()

Dim strIDD as String
strIDD = [Purchase Orders Subform].Form!UnitsReceived

If CurrentDb.OpenRecordset(strIDD).Value > 0 THEN

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 = 38

[Purchase Orders Subform].Form.Recordset.MoveFirst

While Not [Purchase Orders Subform].Form.Recordset.EOF

   

   .Cells(xx, 2).Value = [Purchase Orders Subform].Form!UnitsRecieved

   .Cells(xx, 4).Value = [Purchase Orders Subform].Form!PartNumber & " -" & [Purchase Orders Subform].Form!ItemDescription

   .Cells(xx, 15).Value = [Purchase Orders Subform].Form!UnitPrice


    [Purchase Orders Subform].Form.Recordset.MoveNext

    xx = xx + 1

Wend




End With

objXxL.Visible = True
objXxL.Workbooks.Close

End If
End Sub

 

[DJkarl]

Can anyone explain to me what I'm doing wrong with this?
I've been at this problem for over a week and its driving me mad. Nothing I try works.

I have a subform within a form.

This code lies in the main form on a command button cmdQC9 in an on_Click procedure.

If a value greater than 0 is present in the textbox on the subform I want to move cell data from both forms to an excel spreadsheet.

The subform is a datasheet.

The textbox UnitsReceived

Will someone please explain what I'm doing wrong?

Thanks,

Marty

Private Sub cmdQC9_Click()

Dim strIDD as String
strIDD = [Purchase Orders Subform].Form!UnitsReceived

If CurrentDb.OpenRecordset(strIDD).Value > 0 THEN

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 = 38

[Purchase Orders Subform].Form.Recordset.MoveFirst

While Not [Purchase Orders Subform].Form.Recordset.EOF

   

   .Cells(xx, 2).Value = [Purchase Orders Subform].Form!UnitsRecieved

   .Cells(xx, 4).Value = [Purchase Orders Subform].Form!PartNumber & " -" & [Purchase Orders Subform].Form!ItemDescription

   .Cells(xx, 15).Value = [Purchase Orders Subform].Form!UnitPrice


    [Purchase Orders Subform].Form.Recordset.MoveNext

    xx = xx + 1

Wend




End With

objXxL.Visible = True
objXxL.Workbooks.Close

End If
End Sub

The very first thing I see is this line

If CurrentDb.OpenRecordset(strIDD).Value > 0 THEN

What are you trying to accomplish with this line of code? The Openrecordset method returns a recordset object and must be called like

Set rs = Currentdb.Openrecordset("Tablename or SQL Statement Here")

 

[Brianwarnock]

I was worried about this
strIDD = [Purchase Orders Subform].Form!UnitsReceived

should it be
strIDD = me![Purchase Orders Subform].Form.UnitsReceived

Brian

 

[mdschuetz]

What would you suggest?

I've tried...

If strIDD > 0 THEN

and it doesn't work either. Nothing I've tried works yet.

 

[DJkarl]

Try:

strIDD = Me.SubFormName.Controls("UnitsReceived").Value

 

[Brianwarnock]

What would you suggest?

I've tried...

If strIDD > 0 THEN

and it doesn't work either. Nothing I've tried works yet.

You don't seem to want to comment on the 2 posts so far, one last thing , have you looked at what's in strIDD, with say a MsgBox?

Brian

 

[mdschuetz]

I'm sorry guys, I'm trying all the things you're suggesting. I'm actually really new at VBA, just sort of dove in and now I'm over my head I guess.

I'm trying each suggestion I get.

How can I look at the string in a message box? I assume you want me to try this to see if its holding a value at all?

Marty

 

[Brianwarnock]

MsgBox strIDD

place this after your strIDD= statement

Brian

 

[mdschuetz]

Yes it shows the first value, which is 5 in the proper field I called for.

 

[mdschuetz]

Well,

Its sort of working, but not really, it is posting everything over, even the rows with a value of 0, so the If statement code is doing absolutely nothing at all I guess.

 

[Brianwarnock]

So If strIDD >0 Then

should take you through your code.

Brian

You posted before me.

 

[Brianwarnock]

Maybe I'm missing something but you only test strIDD once, do you need to repeat the test for each row?

Brian

Ok I think I got confused there, when you said row I geuss we were talking about a new subform with 0 in the units recieved control.
You could try a poitive approach to 0
If strIDD =0 Then
Exit Sub
Else
the rest of your code