Populating a Subform item to another subform item.

[radink]

Hey all.

I'm trying to get a combo box to populate the box next to it.

Basically pick a project number in the 1st box and have the project name show up in the second box.

I have it working, but the problem is that when it puts in the name of the project, it fills the whole column with that name, and not just the one box.

Here is what it's doing:

Here is the code im using.

Private Sub ProjectNumber_AfterUpdate()
On Error Resume Next

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String


ProjectNumber.SetFocus
If ProjectNumber.Value > 0 Then
strSQL = "SELECT * FROM ProjectList WHERE ID = " & ProjectNumber.Value

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.ProjectName = rs("ProjectName")
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If

End Sub

Let me know what I can do.

Thanks!!

 

[Ian C]

I'd use a slighltly different and simpler approach by changing the RowSource of project Number dropdown box to include the ProjectName field -

"SELECT ProjectList.ProjectNumber, ProjectList.ProjectName FROM ProjectList;"

You can then reference the second column in the after update sub as follows:

Private Sub ProjectNumber_AfterUpdate()
Me.ProjectName = Me.ProjectNumber.Column(1)
End Sub

Ian

 

[radink]

Thanks Ian!

I'm trying to do this again with a 2nd box.

SELECT ProjectList.ProjectNum, ProjectList.ProjectName, ProjectList.ProjectClient FROM ProjectList;

Private Sub ProjectNumber_AfterUpdate()

Me.ProjectName = Me.ProjectNumber.Column(1)
Me.ProjectClient = Me.ProjectNumber.Column(2)
End Sub

It works but doesn't. I don't get errors, but the 2nd box doesn't fill with any data. In fact when I select the 1st one, it erases what ever is in there. The 1st box however works just fine.

Any ideas?

 

[radink]

Thanks Ian!

I'm trying to do this again with a 2nd box.

SELECT ProjectList.ProjectNum, ProjectList.ProjectName, ProjectList.ProjectClient FROM ProjectList;

Private Sub ProjectNumber_AfterUpdate()

Me.ProjectName = Me.ProjectNumber.Column(1)
Me.ProjectClient = Me.ProjectNumber.Column(2)
End Sub

It works but doesn't. I don't get errors, but the 2nd box doesn't fill with any data. In fact when I select the 1st one, it erases what ever is in there. The 1st box however works just fine.

Any ideas?

I think I narrowed it down to it being the Column(2) part, but in the table it is the column right next to the project name column. I've even tried going up to 12 and I get nothing.

I'm stumped.

 

[radink]

Can anyone help me with this?

 

[ansentry]

Have a look at the sample I posted here. it is the first one and it is called Fill in controls on subform.zip