[NeoTifa]

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.

[P4L]

Could you provide a screen shot and all the VBA code for this? Your SQL is fine.

[lastpilgrim]

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

NeoTifa, 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.

[NeoTifa]

Heres 2 action shots.

Attached image(s)

• 
• 

[NeoTifa]

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?

[NeoTifa]

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

[raziel_]

at what line you get the error ? also try to set Optional Criteria as string

[NeoTifa]

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

[raziel_]

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

[NeoTifa]

But the orderby clause is optional, and she left it out? (I'm appending code to something someone else did)

[raziel_]

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

[raziel_]

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

[NeoTifa]

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

Thanks, I'll make said adjustments. <3

[NeoTifa]

NoBrain, 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.

[raziel_]

i am a bit confused. can i see the code that work if it is not much of a trouble to You