Need Help Getting the Combo Box to Update My Text Boxes

[airhendrix13]

Hi,

I want to try to get my combo box to update the rest of my text boxes. For example, I have Column A, B, and C in my combo box, when I select for example A, I want all the information for row A to show up in my text boxes. I have several columns in each row in my table and each column in the table should correspond to a text box in my form.

Here is what I have.

Private Sub Combo26_AfterUpdate()
Dim rs As Object,
Dim x As Integer
If Not (IsNull(Me!cbo_Surname)) Then

x = cbo_Surname
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tenantid] = " & x
If rs.NoMatch Then
MsgBox ("cannot find this tenant")
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close

End If
Set rs = Nothing
End Sub

I put this in "AfterUpdate" but I keep getting a compiling error. Any suggestions or just new ideas to accomplish my task easier?

Thanks a lot in advance!

P.S. I'm new to access so please keep that in mind.

 

[boblarson]

What line is highlighted when you break for the error?

 

[Dreamweaver]

If Not (IsNull(Me!cbo_Surname)) Then

This dont look right

Change to
If Not IsNull(Me!cbo_Surname) Then

 

[Dreamweaver]

Also you could get an error hear:

Set rs = Nothing

Move that line above the "end if" so it only sets rs when rs has been created.

 

[airhendrix13]

Private Sub Combo26_AfterUpdate()

This is the line of code that gets highlighted.

If anybody knows of any ways to get my task accomplished without using additional macros that would be even better

Thanks again guys

 

[airhendrix13]

Also "Dim rs As Object" is red, does that mean there is a problem with that at all?

 

[Dreamweaver]

Dim rs As Object,
Remove the "," from the end

 

[pbaldy]

Dump the comma

 

[airhendrix13]

Ok so I dumped the comma but now I have a new error!
"run-time error '2465' Microsoft Access can't find the field "cbo_surname" refered to in your expression.

JAARRRR!

Thanks again.

 

[boblarson]

First, make sure you have a combo box by that exact name.
Second, change Me!cbo_Surname to Me.cbo_Surname

 

[Dreamweaver]

Try chnging this from Me!cbo_Surname to either cbo_Surname or Me![cbo_Surname] not sure if thats the cause or not LOL

 

[airhendrix13]

Okay for the name of the combo box do you mean the name that sits next to it on the form or what?

Because if thats the case the name of it is RS Number and I get an error.

Sorry guys to waste your time but this has been an issue for a while and i need to get it fixed.

 

[airhendrix13]

I'm also Access retarded if you haven't noticed already. LOL

 

[boblarson]

No, when you select the actual combo box and then look in the properties box, or up on the top left toolbar (if in the default location) and it will show you the actual name of the combo box. If you used the wizard to create your form it will be the same name as your field that it is bound to. If not, unless you changed it the name will be something like Combo25.

 

[airhendrix13]

Ok I changed all the surname stuff to Combo26 like it said in my properties and I still get a "compiling error method or member not found" and this gets high lighted in blue ".cbo_Combo26" and this in yellow "Private Sub Combo26_AfterUpdate()"

 

[airhendrix13]

Heres the whole macro...

Private Sub Combo26_AfterUpdate()
Dim rs As Object
Dim x As Integer
If Not (IsNull(Me.cbo_Combo26)) Then

x = cbo_Combo26
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tenantid] = " & x
If rs.NoMatch Then
MsgBox ("cannot find this tenant")
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close

End If
Set rs = Nothing
End Sub

 

[boblarson]

Okay, remove the check for null as it isn't necessary since the code is on the AFTERUPDATE event of the same combo box. If it gets to the AfterUpdate event, the combo will not be null.

 

[airhendrix13]

Ok that part now seems to be solved but now it high lights the "end if" in blue and shows the compile error message again.

Private Sub Combo26_AfterUpdate() <-----this is high lighted in yellow
Dim rs As Object
Dim x As Integer

x = cbo_Combo26
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tenantid] = " & x
If rs.NoMatch Then
MsgBox ("cannot find this tenant")
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close

End If <---------- thats high lighted in blue
Set rs = Nothing
End Sub

 

[boblarson]

That's because there is no more IF clause checking for null. Just remove that last End If

 

[airhendrix13]

ok so now i get this error

Private Sub Combo26_AfterUpdate()
Dim rs As Object
Dim x As Integer

x = cbo_Combo26
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tenantid] = " & x
If rs.NoMatch Then
MsgBox ("cannot find this tenant") <--------high lighted yellow
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close

Set rs = Nothing
End Sub

the microsoft jetbase database engine doesnt recognize tenantid as a valid field name or expression.

So what do i put there?

I'll be sure to add to your rep after this seeing uve been so helpful so far.

thanks again