s c u z m i ? (skuzmi) wrote in visualbasic,
s c u z m i ?
skuzmi
visualbasic

  • Mood:

help a VB newbie with simple debugging

This is driving me nuts, not knowing why it doesn't work. I hope there's some kind soul in this com who can and will help me out. Not sure what the rules are, I checked the info, so I'll just put my code behind an LJ-cut. Hope this is ok, let me know if I did something wrong.

The problem: I cannot extract the customer_no and employee_no data out. Its all random what is returned onto my spreadsheet. I've been debugging for days and can't figure it out. Please please help?
the database can be found here fss2000.mdb


Option Explicit
Dim filter As String
Dim filtercriteria As Boolean



Private Sub cbconame_Click()

End Sub

Private Sub cbofilter_Change()

' Determine the filter selected


filter = cbofilter.Value

If filter <> "" And filter <> "No filter" Then
filtercriteria = True
End If

End Sub


Private Sub cmd_cancel_Click()

' Close the form

UserForm.Hide


End Sub

Private Sub cmd_ok_Click()


GetRecords1

End

End Sub

Private Sub UserForm_Initialize()

filtercriteria = False

With cbofilter
.List = Array("Customer_No", "Employee_No", "Order_No", "No filter")
End With

End Sub



Sub GetRecords1()

' This program gets all the records out of a table by using
' The GetRows method of the Recordset object. The records are
' put into a variant variable in an array.

Dim WrkDefault As Workspace
Dim FSS2000 As Database
Dim RS1 As Recordset
Dim DatabaseName As String
Dim SQLString As String
Dim SQLString2 As String
Dim str1 As String
Dim tblstring As String
Dim cndstring As String
Dim cndstring2 As String
Dim cndstring3 As String
Dim sign As String
Dim orderchoice As String
Dim signcriteria As Boolean
Dim qtyAentered As Boolean

qtyAentered = False
signcriteria = False


On Error Resume Next

DatabaseName = ThisWorkbook.Path & "\FSS2000"
Set WrkDefault = DBEngine.Workspaces(0)
Set FSS2000 = WrkDefault.OpenDatabase(Name:=DatabaseName, ReadOnly:=False)



'selecting relevant customer records to display

str1 = "product_a_quantity"

If cbcustnum.Value = True Then
str1 = str1 + ", customer_no"
End If

If cbconame.Value = True Then
str1 = str1 + ", co_name"
End If

If cbcity.Value = True Then
str1 = str1 + ", city"
End If

If cbstate.Value = True Then
str1 = str1 + ", state"
End If


'employee records
If cbempnum.Value = True Then
str1 = str1 + ", employee_no"
End If

If cbsurname.Value = True Then
str1 = str1 + ", surname"
End If

If cbname.Value = True Then
str1 = str1 + ", name"
End If

If cbbase.Value = True Then
str1 = str1 + ", base_pay"
End If

If cbcom.Value = True Then
str1 = str1 + ", commission"
End If

'order records


If cbordnum.Value = True Then
str1 = str1 + ", order_no"
End If

If cbmonth.Value = True Then
str1 = str1 + ", month"
End If

If cbpdtb.Value = True Then
str1 = str1 + ", product_b_quantity"
End If

If cbpdtc.Value = True Then
str1 = str1 + ", product_c_quantity"
End If



'determine sign
If obsmaller.Value = True Then
sign = "<"
End If

If obequal.Value = True Then
sign = "="
End If

If obbigger.Value = True Then
sign = ">"
End If

If txtqty.Value <> "" Then
qtyAentered = True
End If

If sign <> "" And qtyAentered = True Then
sign = sign + " " + txtqty.Value
signcriteria = True
End If


'construct strings

tblstring = "Orders, Customers, Employees"
cndstring = "Orders.Customer_No = Customers.Customer_No"
cndstring2 = "Orders.Employee_No = Employees.Employee_No"


'selecting relevant orders records to display
SQLString = "SELECT " & str1 & " FROM " & tblstring & " WHERE " _
& cndstring


If signcriteria = False And filtercriteria = False Then
SQLString2 = " AND " & cndstring2
End If
If signcriteria = True And filtercriteria = True Then
SQLString2 = " AND " & cndstring2 & " AND product_a_quantity " _
& sign & " ORDER BY " & filter
End If
If signcriteria = False And filtercriteria = True Then
SQLString2 = " AND " & cndstring2 & " ORDER BY " & filter
End If
If signcriteria = True And filtercriteria = False Then
SQLString2 = " AND " & cndstring2 & " AND product_a_quantity " _
& sign
End If



Set RS1 = FSS2000.OpenRecordset(Name:=SQLString + SQLString2, Type:=dbOpenDynaset)

'debug
ActiveCell.Offset(0, 1) = SQLString + SQLString2

Worksheets("Sheet1").Range("a1").CopyFromRecordset RS1

FSS2000.Close

End Sub
Subscribe
  • Post a new comment

    Error

    default userpic

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 3 comments