UtterAccess Discussion Forums > Checking to see if a field exists

Full Version: Checking to see if a field exists

UtterAccess Discussion Forums > Microsoft® Access > Access Reports

AxesWannabe

Mar 14 2006, 02:26 PM

Good morning UA!!!

I have been working on a report that I feel is nearing completion, however, my code doesn't seem to be working quite how I expected it...

I have a report for last month's program usage by low-income residents, i.e. each usage for food, shelter, clothing, etc. That report has a crosstab query as it's record source. The problem is that there is 1 program that may or may not be present each time the query is run. I have created an unbound textbox for that 1 program and IF the program is used last month, then I'd like for it's control source to change to the newly included field "Back to School", because in normal circumstances, it's not so until needed the control source is unbound. Here is the code I'm using in the report's OnOpen event and the function to check if the field exists:

CODE

Private Sub Report_Open(Cancel As Integer)

    If FieldExists("qryMSR_CT_LastFullMonthEthnicitiesXPrograms", "Back to School") = True Then
    ' Field exists
        Me.txtB2School.ControlSource = "Back to School"
    Else
    ' Field does not exist
        Me.txtB2School.Visible = False
        Me.BacktoSchool_Label.Visible = False

    End If

End Sub

Public Function FieldExists(queryName As String, fieldName As String) As Boolean
On Error GoTo FieldExists_Exit

    Dim fld As Field

    For Each fld In CurrentDb.QueryDefs(queryName).Fields
        If fld.Name = fieldName Then
           FieldExists = True
           Exit For  'Or Exit Function
        End If
    Next

FieldExists_Exit:
End Function

My problem is even if I change the field to a field that I know is in there....the code still returns a false and I don't know why....can anyone help?

Thank you for any and all help you can provide!!!

fkegley

Mar 14 2006, 02:58 PM

Try this:

Dim fld As DAO.Field

You may also need to set a reference to the Data Access Objects x.x library for your version of Access.

AxesWannabe

Mar 14 2006, 03:14 PM

Okay, now you got me somewhere....thank you, BTW....but now this line of code:

CODE

If fld.Name = fieldName Then

fld.Name is showing a field I never indicated ("Ethnicity")
fieldName is showing the field I'm analyzing ("Back to School")

Where is the fld.Name coming from? I'm confused....Am I making sense??? I think I'm confusing myself....

Thank you Frank!

fkegley

Mar 14 2006, 03:43 PM

You're welcome. I am glad I could help.

As for your new question:

fld.Name is coming from the

For Each fld In CurrentDb.QueryDefs(queryName).Fields

line of code

You told it to look at each field in the query with the For Each fld blah blah blah line of code.

Edited by: fkegley on Tue Mar 14 15:44:03 EST 2006.

AxesWannabe

Mar 14 2006, 03:53 PM

OMG....okay...maybe my connection was really slow because it had just stopped analyzing at the Ethnicity....it's working as I expected....I was thinking OMG what happened when I changed that to the DAO reference...I'm good now...and now I'm rambling! Thank you Frank!!!!

fkegley

Mar 14 2006, 03:54 PM

Ramblin' Rose, Ramblin' Rose---I believe it was Nat King Cole who sang it best.

You're welcome. I am glad I could help.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.