Populate Text Box based upon Combo Box Selection

[titobudd]

I am currently trying to create a form that will update the text in a series of text boxes based upon the selection of a value in a combobox. All of the data updated is tied to either the same table as the combo box or to other tables in the database tied by primary keys.

I'm trying to get one text box to update and it doesn't want to work.

Here is my code thus far:

Option Compare Database
Dim localConnection As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cboID_AfterUpdate()

Dim sql As String

'On Error GoTo DbError

sql = "SELECT * " & _
"FROM SignMainGeneral " & _
"WHERE ID = " & Me.cboID.Value

rs.Open sql, localConnection, , , adCmdText

'Me.txtMUTCDCode.SetFocus

If rs.BOF = True Then
'populate with MUTCD code
Me.txtMUTCDCode = rs!MUTCDCode
Else
MsgBox "Error."
End If

rs.Close

Set rs = Nothing

Exit Sub

'DbError:

'MsgBox "There was an error retrieving information " & _
"from the database. " & Err.Number & ", " & Err.Description

rs.Close

Set rs = Nothing

End Sub





Private Sub Form_Load()

On Error GoTo ConnectionError

Set localConnection = CurrentProject.Connection

MsgBox "Local connection established."

Exit Sub

ConnectionError:

MsgBox "Error Connecting"



End Sub

Public Sub Form_Unload(Cancel As Integer)

'localConnection.Close
'rs = Nothing

End Sub



It seems my recordset doesn't return values when it should. The MUTCDCode value returned by the recordset appears to be NULL. I will then get an error saying "the value you have entered isn't valid for this field."

I am very new to creating forms in Access. I'm not even sure if I need to use VBA to accomplish my goal. Any help would be much appreciated. Thanks.

 

[CBrighton]

BoF is not the first record, it's before it.

You need a rs.movefirst before you start checking for values of certain fields. Movefirst will select the first record in the recordset, movenext can be used to loop through the records if required.

:edit:

Replace:

If rs.BOF = True Then
'populate with MUTCD code
Me.txtMUTCDCode = rs!MUTCDCode
Else
MsgBox "Error."
End If

With:

If rs.EOF = True Then
'No Records
    MsgBox "Error."
    Exit sub
End If
rs.movefirst
Me.txtMUTCDCode = rs!MUTCDCode

 

[titobudd]

I changed the BOF to EOF and added the rs.movefirst. It is returning TRUE for the EOF check, even though it shouldn't be, as far as I can tell. Every entry in the combo box should return something to populate the text box.

 

[CBrighton]

use "debug.print sql" to get the SQL string, then copy & paste it into a new query to see if it has records.

 

[vbaInet]

Perform a RecordCount before attempting to MoveFirst.

If rst.RecordCount <> 0 Then
     rst.MoveFirst
     .... other code ...
End If

 

[CBrighton]

Perform a RecordCount before attempting to MoveFirst.

Would that be more accurate than just testing EoF?

My understanding is that EoF on recordset load would only be true if there were no records?

 

[DCrake]

----------------------------
BOF
----------------------------
First Record
......
Last Record
----------------------------
EOF
----------------------------

The most accurate test or empty recordsets is

If Not Rs.BOF And Not Rs.EOF Then
    'Records exist
Else
    'No Records exist
End If

 

[vbaInet]

Would that be more accurate than just testing EoF?

My understanding is that EoF on recordset load would only be true if there were no records?

Yep!

The actual test for no records using BOF and EOF is:

If Not [COLOR=Red]([/COLOR]rst.BOF [COLOR=Red]And[/COLOR] rst.EOF[COLOR=Red])[/COLOR] Then

When they are both true, no records exist. Easier to use RecordCount.

 

[CBrighton]

Is there ever any situation where a recordset has just been set and EoF is true but there are records?

If not then EoF is enough of a test, no need to check BoF too (which I would expect to always be true regardless of the number of records, unless MoveFirst has been used before checking for BoF)

 

[DCrake]

Think of your table as you would a book. Every book has a front cover and a back cover. These are effectively your BOF and EOF.
When you open the book you are at page 1 if there are any pages in it. Now in order to find out how many pages thre are in the book you need to go to the last page to find out.
My normal syntax is as follows

Dim Rs As DAO.Recordset
Dim rCnt As Long

Set Rs = CurrentDb.OpenRecordset("TableOrQuery")

If Not Rs.BOF And Not Rs.BOF Then
  [COLOR="DarkGreen"] 'Optional[/COLOR]   
   Rs.MoveLast
   rCnt = Rs.Recordset
   Rs.MoveFirst
   
   Do Until Rs.EOF

       [COLOR="Blue"] ...Code Here[/COLOR]

       Rs.MoveNext
   Loop
   Rs.Close[COLOR="darkgreen"] ' Does not work on an empty recordset[/COLOR]
End If
Set Rs = Nothing

 

[vbaInet]

Test it for yourself.

Run a BOF and EOF test on:

1. an empty table
2. a table with one or more records.

... without moving.

 

[CBrighton]

I'm not disputing how BoF & EoF work.

I'm saying why would you need to test more than EoF? Surely if EoF is true then there are no records. That's why I was asking if there was any situation where EoF is true but there are records... to see if there were any.

 

[DCrake]

If you are at the last record in the table then issue a .MoveNext followed by a Loop. The ordinal position will move next to the table footer (EOF) then the Do Until Rs.EOF will then be True so it then exits the Do Until Loop

However if you are at BOF and try to movenext the only place it can go is to the EOF which it cannot because it did not have a previous location.

 

[vbaInet]

Ok I will explain.

To test EOF you need to MoveLast to be sure you're at the EOF. In an empty recordset you cannot call the MoveLast method because it will throw an error. But in a recordset with thousands of records you can call the MoveLast method to get to EOF. Does this help?

 

[titobudd]

I performed the "Debug.Print sql" as suggested by CBrighton. I believe this has proven to be useful as here is the result:

SELECT MUTCDCode FROM SignMainGeneral WHERE ID = 013224404-1

When running this query on the table it returns an empty record for the field I am trying to extract data from. When I make these changes,

SELECT MUTCDCode FROM SignMainGeneral WHERE ID = "173224309-10";

I receive the desired result. How would go about recreating this in my vba code? Thanks.

 

[vbaInet]

WHERE ID = 013224404-1

WHERE ID = "173224309-10";
I receive the desired result. How would go about recreating this in my vba code?

I don't see how this ties in with your original post and I don't understand what you mean. If you want to use that WHERE clause then include it type it in the variable.

I think you want to investigate using a subform instead for this whole process. Then link the subform to the combo box via the subform's Link Master Fields and Link Child Fields properties.

 

[titobudd]

I should have added this to my last post. The value for the query needs to be dynamic based upon the selection of the combobox. This is what my vba query looks like right now:

sql = "SELECT * " & _
"FROM SignMainGeneral " & _
"WHERE ID = " & Me.cboID

How do I allow it to be dynamic yet still have the double quotes ("cboID selection") in the query? Thanks.

 

[vbaInet]

I should have added this to my last post. The value for the query needs to be dynamic based upon the selection of the combobox. This is what my vba query looks like right now:

sql = "SELECT * " & _
"FROM SignMainGeneral " & _
"WHERE ID = " & Me.cboID

How do I allow it to be dynamic yet still have the double quotes ("cboID selection") in the query? Thanks.

sql = "SELECT * " & _
"FROM SignMainGeneral " & _
"WHERE ID = [COLOR=Red]'[/COLOR]" & Me.cboID & "[COLOR=Red]'[/COLOR]"

 

[titobudd]

I think you want to investigate using a subform instead for this whole process. Then link the subform to the combo box via the subform's Link Master Fields and Link Child Fields properties.

Would this method not require the use of VBA? As time has gone on I have been wondering if there is an easier way to do what I need to do. Thanks.

 

[vbaInet]

Would this method not require the use of VBA?

Nope, it won't!