[VBA] Is this SQL statement wrong?

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 2367 Views - Last Post: 22 July 2010 - 06:43 AM Rate Topic: -----

#1 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

[VBA] Is this SQL statement wrong?

Posted 15 July 2010 - 10:04 AM

SELECT tblPeople.LastName, tblPeople.FirstName, tblPeople.OrganizationID FROM tblPeople WHERE (((tblPeople.IsInActive)=False));



I checked all the variable names and junk, and they match, but it's not showing up in the columns like they're supposed to. :( It's bound to a combo box btw.

Is This A Good Question/Topic? 0
  • +

Replies To: [VBA] Is this SQL statement wrong?

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: [VBA] Is this SQL statement wrong?

Posted 15 July 2010 - 10:20 AM

Could you provide a screen shot and all the VBA code for this? Your SQL is fine.
Was This Post Helpful? 1
  • +
  • -

#3 lastpilgrim  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 2
  • Joined: 15-March 10

Re: [VBA] Is this SQL statement wrong?

Posted 15 July 2010 - 10:36 AM

As far as i can see the SQL should work.

Have you got it stored as a query or is it in the row source property under the data tab?

also if your not getting columns what are you getting?

Also check under the format tab the Column Widths property you should have 3 values
ie 5cm;5cm;1cm dependant on the combo box width and field data lengths.

Hope that helps

Graham




View PostNeoTifa, on 15 July 2010 - 09:04 AM, said:

SELECT tblPeople.LastName, tblPeople.FirstName, tblPeople.OrganizationID FROM tblPeople WHERE (((tblPeople.IsInActive)=False));



I checked all the variable names and junk, and they match, but it's not showing up in the columns like they're supposed to. :( It's bound to a combo box btw.

Was This Post Helpful? 1
  • +
  • -

#4 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

Re: [VBA] Is this SQL statement wrong?

Posted 15 July 2010 - 11:24 AM

Heres 2 action shots.

Attached image(s)

  • Attached Image
  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#5 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

Re: [VBA] Is this SQL statement wrong?

Posted 15 July 2010 - 01:14 PM

Derp derp. XD

Okay, so now is it possible to replace that tblPeople.OrganizationID with its appropriate caption as defined by the tblOrganizations.OrgName field? That way, instead of just naming of a key number, it will actually say the organizations name in the row? Is that some type of fancy pants wizardry going on?
Was This Post Helpful? 0
  • +
  • -

#6 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 06:13 AM

Okay, nevermind, that was solved by P4L via text as well.

Okay, so now I'm getting some type of parameter error, which doesn't make sense, because 2 of them are Optional.

'error
Too few parameters. Expected 1.
ICNumber      'also for RoomNumber and Locaation Description
tblPeople
[PersonID]=9



Heres the actual calls under Form_frmPeopleList
Me.cICNumber = getICNumber(getPhoneID(currentPID))
Me.cRoomNumber = getRoomNumber(getPhoneID(currentPID))
Me.cLocationDescription = getLocationDescription(getPhoneID(currentPID))



Under LookupFunctions Module thingy
Function getICNumber(idx) As Integer
rtnval = IIf(IsNull(idx), "", Elookup("ICNumber", table, tableID & idx))
getICNumber = rtnval
End Function

Function getRoomNumber(idx) As String
rtnval = IIf(IsNull(idx), "", Elookup("RoomNumber", table, tableID & idx))
rtnval = IIf(IsNull(rtnval), "", rtnval)
getRoomNumber = rtnval
End Function

Function getLocationDescription(idx) As String
rtnval = IIf(IsNull(idx), "", Elookup("LocationDescription", table, tableID & idx))
rtnval = IIf(IsNull(rtnval), "", rtnval)
getLocationDescription = rtnval
End Function



abElookup
Option Compare Database

Function Elookup(Expr As String, Domain As String, Optional Criteria, Optional OrderClause)
On Error GoTo Err_ELookup
    'Purpose: Faster and more flexible replacement for DLookup()
    'Arguments: Same as DLookup, with additional Order By option.
    'Author: Allen Browne. allen@allenbrowne.com
    'Examples:
    '1. To find the last value, include DESC in the OrderClause, e.g.:
    ' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    ' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note: Requires a reference to the DAO library.
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strsql As String
    
    'Build the SQL string.
    strsql = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strsql = strsql & " WHERE " & Criteria
    End If
    If Not IsMissing(OrderClause) Then
        strsql = strsql & " ORDER BY " & OrderClause
    End If
    strsql = strsql & ";"

    'Lookup the value.
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(strsql, dbOpenForwardOnly)
    If rs.RecordCount = 0 Then
        Elookup = Null
    Else
        Elookup = rs(0)
    End If
    rs.Close

Exit_ELookup:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Err_ELookup:
    MsgBox Err.Description & vbCrLf & Expr & vbCrLf & Domain & vbCrLf & Criteria, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
    End Function
Function ECount(Expr As String, Domain As String, Optional Criteria, Optional OrderClause)
On Error GoTo Err_ECount
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strsql As String

    'Build the SQL string.
    strsql = "SELECT " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strsql = strsql & " WHERE " & Criteria
    End If
    If Not IsMissing(OrderClause) Then
        strsql = strsql & " ORDER BY " & OrderClause
    End If
    strsql = strsql & ";"

    'Lookup the value.
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(strsql)
'    If rs.RecordCount = 0 Then
'        Elookup = Null
'    Else
'        Elookup = rs(0)
'    End If
    If rs.EOF Then
      ECount = 0
    Else
      rs.MoveFirst
      rs.MoveLast
      ECount = rs.RecordCount
    End If
    rs.Close

Exit_ECount:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Err_ECount:
    MsgBox Err.Description, vbExclamation, "ECount Error " & Err.Number
    Resume Exit_ECount
    End Function


Was This Post Helpful? 0
  • +
  • -

#7 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 06:34 AM

at what line you get the error ? also try to set Optional Criteria as string
Was This Post Helpful? 0
  • +
  • -

#8 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 06:39 AM

Wtf? I don't know, I posted the lines I was getting errors at! I also posted the exact error, and what funcitons are responsible/effected by it. And it is opitonal. I already said that! XD
Was This Post Helpful? 0
  • +
  • -

#9 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 06:49 AM

oh ok tr the query:
    strsql = "SELECT " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strsql = strsql & " WHERE " & Expr & "=" & Criteria
    End If



EDIT: also orderby clause must have the type (ASC, DESC)

This post has been edited by NoBrain: 19 July 2010 - 06:50 AM

Was This Post Helpful? 0
  • +
  • -

#10 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 09:25 AM

But the orderby clause is optional, and she left it out? (I'm appending code to something someone else did)
Was This Post Helpful? 0
  • +
  • -

#11 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 10:40 AM

the other thing that i can think why this happening is if you send a field that dont exist in your case in ECount() Expr dont send correct field or Domain send incorrect table.

EDIT: or try adding quotes at the where clause like so :
If Not IsMissing(Criteria) Then
    strsql = strsql & " WHERE " & Expr & "= '" & Criteria & "'"
End If


This post has been edited by NoBrain: 19 July 2010 - 10:43 AM

Was This Post Helpful? 1
  • +
  • -

#12 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 10:59 AM

another thing i notice:
If rs.EOF Then
      ECount = 0
    Else
      rs.MoveFirst
      rs.MoveLast
      ECount = rs.RecordCount
    End If
    rs.Close



is wrong there can also be EOF and the recordset to return recordcoun different from 0. since this function return the recordcount it is easy to just return the rs.recordcount. the part when the record move first and then last is useless and dont effect how many records the record set have :)

This post has been edited by NoBrain: 19 July 2010 - 11:00 AM

Was This Post Helpful? 1
  • +
  • -

#13 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

Re: [VBA] Is this SQL statement wrong?

Posted 19 July 2010 - 11:05 AM

:o Damn, she's been using someone else's defective code?! XD

Thanks, I'll make said adjustments. <3
Was This Post Helpful? 0
  • +
  • -

#14 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2796
  • View blog
  • Posts: 15,910
  • Joined: 24-September 08

Re: [VBA] Is this SQL statement wrong?

Posted 20 July 2010 - 10:23 AM

View PostNoBrain, on 19 July 2010 - 11:40 AM, said:

the other thing that i can think why this happening is if you send a field that dont exist in your case in ECount() Expr dont send correct field or Domain send incorrect table.

EDIT: or try adding quotes at the where clause like so :
If Not IsMissing(Criteria) Then
    strsql = strsql & " WHERE " & Expr & "= '" & Criteria & "'"
End If



Okay, I tried this, and it just made it worse. v__v I'm seriously about to just rewrite the whole project. The funny thing is, when I commented out the call to start all this catasrophe on another similar form, it works perfectly.
Was This Post Helpful? 0
  • +
  • -

#15 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: [VBA] Is this SQL statement wrong?

Posted 20 July 2010 - 11:01 AM

i am a bit confused. can i see the code that work if it is not much of a trouble to You :)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2