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

  • 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


End Sub

Private Sub cmd_ok_Click()



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)

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

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


End Sub
  • Post a new comment


    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.

I tried to download your .mdb file from the link, but it went to the Geocities "Page unknown" message. I'd be willing to have a look, but I'd need to see the database in it's entirety.

You also mention: "[...]what is returned onto my spreadsheet[...]". Are you linking to Excel or did you mean table, as in database table?
thanks for your interest in helping. Alas, too late, I handed it in in a total mess, the program works randomly. It was nice of you to offer though, very appreciated :)
You know where I am for next time!