My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Checking to see if a field exists
|
|
 Mar 14 2006, 02:26 PM
Post
#1
|
|
|
UtterAccess Addict Posts: 185 From: Moreno Valley, CA, USA  |
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!!! |
 |
 
|
|
Mar 14 2006, 02:58 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 23,583 From: Mississippi |
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. |
|
|
|
|
Mar 14 2006, 03:14 PM
Post
#3
|
|
|
UtterAccess Addict Posts: 185 From: Moreno Valley, CA, USA |
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! |
|
|
|
|
Mar 14 2006, 03:43 PM
Post
#4
|
|
|
UtterAccess VIP Posts: 23,583 From: Mississippi |
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. |
|
|
|
|
Mar 14 2006, 03:53 PM
Post
#5
|
|
|
UtterAccess Addict Posts: 185 From: Moreno Valley, CA, USA |
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!!!!
|
|
|
|
|
Mar 14 2006, 03:54 PM
Post
#6
|
|
|
UtterAccess VIP Posts: 23,583 From: Mississippi |
Ramblin' Rose, Ramblin' Rose---I believe it was Nat King Cole who sang it best.
You're welcome. I am glad I could help. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 18th May 2013 - 10:29 PM |