Recordset

[Woftysofty]

Hi, I am trying to write some code to show or hide some controls on a form depending if the value of another control on same the form is a value in my recordset.

The code I have attempted to develop so far, below, works (kinda), the problem is the cursor in my recordset seems to be stuck on the last record or EOF and does not loop through all my recordset values everytime the code is run. Please help someone.

Private Sub Contact_Name_AfterUpdate()

Dim cnnContactsList As ADODB.Connection
Set cnnContactsList = CurrentProject.Connection
Dim rsContactsList As New ADODB.Recordset
rsContactsList.ActiveConnection = cnnContactsList
rsContactsList.CursorType = adOpenKeyset
rsContactsList.LockType = adLockReadOnly

Dim sqlCntsLst As String
sqlCntsLst = "SELECT [Contacts List].[Contact Name], [Entity Categories].[Entity Category] FROM ([Entity Categories]"
sqlCntsLst = sqlCntsLst & " INNER JOIN [Entity Sub-Categories] ON [Entity Categories].[Entity Category] = [Entity Sub-Categories].[Entity Category])"
sqlCntsLst = sqlCntsLst & " INNER JOIN [Contacts List] ON [Entity Sub-Categories].[Entity ID] = [Contacts List].[Entity Category]"
sqlCntsLst = sqlCntsLst & " WHERE ((([Entity Categories].[Entity Category])='Business'))"

rsContactsList.Open sqlCntsLst
rsContactsList.MoveFirst

Do While Not rsContactsList.EOF

If Me.Contact_Name.Value = rsContactsList(0).Value Then

TradingName_Label.Visible = True
Trading_Name.Visible = True
ACN_Label.Visible = True
ACN.Visible = True
Trading_Name.SetFocus
Else
Internal_Department.SetFocus
TradingName_Label.Visible = False
Trading_Name.Visible = False
ACN_Label.Visible = False
ACN.Visible = False

End If

rsContactsList.MoveNext
Loop

rsContactsList.Close
cnnContactsList.Close
Set rsContactsList = Nothing
Set cnnContactsList = Nothing

End Sub

 

[evanscamman]

I don't understand what you are trying to do.

If you are trying to hide/show controls based on values in a recordset, how does this work with multiple records from the recordset?

If the 1st record causes the control to be visible, couldn't the second record just hide it?

I don't see the point.

Evan

 

[ajetrumpet]

[I agree with evan. I don't think efficiency is present here. Perhaps a rehash of your objective would be in order, so we could let you know what you could do to remedy this, or suggest a "better way" of doing it.[/I]

 

[jardiamj]

I don't see the point either! But if what you want is to hide the elements on the form if the value of the control exists in you RecordSet. You should try using:
rsContactsList.Find "MyColumnName = '" & Me.Contact_Name.Value & "'"
And pass a True value to a boolean variable if the record was found or False if it wasn't.
I think that should work fine.
I hope it will help.
Cheers.

 

[Woftysofty]

Thanks all for taking the time to look at my issue, and yes looking at it from your angle it was pointless the way I was going about it. Though I think jardiamj has figured out what I am trying to do and I will try using his advice, thanks jardiamj!!

Forgive me for not making sense sometimes, I am only a novice at writing VBA code. Thanks all, you've been a great help!!

 

[jardiamj]

Hello Woftysofty! I'm glad you will give to my advice a try. Post back if it works or not.
Have a good day. Cheers!!