Filling combo boxes with query / table data

[Steve]

I am using Access 2003

Private Sub Form_Load()

Dim rs As DAO.Recordset
Dim strSql As String
Dim LNameArray(0 To 500) As String
Dim LNameCount As Integer

strSql = "SELECT [Last Name] FROM [qryLastNameLookUp];"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
LNameCount = 0

Do While Not rs.EOF
LNameArray(LNameCount) = rs![Last Name]
cboLastName.AddItem (LNameArray(Count))
rs.MoveNext
LNameCount = LNameCount + 1
Loop

rs.Close
Set rs = Nothing

End Sub

I am trying to pull the last names from a query and have them listed in the
combo box as the form loads. The combo box is unbound, but originally its
rowsource was from the query itself. I was getting strange problems with the
wrong records being deleted or appended. I have been programming VB for about
a year but I am only just begining with Access and databases in general.

My form has 3 combo boxes; Last Name, First Name, and Supervisor. Last Name
will have an afterupdate event that will pull all the first names from a
query that have the selected last name. First name will do the same for the
supervisor. So If there are 3 people named Smith and only one of them is
Steve, Steve Smiths supervisor will appear in the appropriate combo box once
Steve is selected from the combo box.

Once I get that, my next step will be to find the record in the Table and
delete it. My problem is understanding how the database objects work. I have
printed out a lot of online resources that I am going to read tonight. But if
anyone has a suggestion I would appriciate the help.

 

[Software-Matters via AccessMonster.com]

Hi,

I would use query criteria to achieve the desired result, as follows.

In the FirstNames combobox recordset (query) the last names field should have
criteria that looks at the LastNames combobox i.e. [forms]![Form Name]!
[LastNames]

On the afterupdate of the LastNames combobox do a requery on the FirstNames
combobox and so on.

Regards
JD

I am using Access 2003

Private Sub Form_Load()

Dim rs As DAO.Recordset
Dim strSql As String
Dim LNameArray(0 To 500) As String
Dim LNameCount As Integer

strSql = "SELECT [Last Name] FROM [qryLastNameLookUp];"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
LNameCount = 0

Do While Not rs.EOF
LNameArray(LNameCount) = rs![Last Name]
cboLastName.AddItem (LNameArray(Count))
rs.MoveNext
LNameCount = LNameCount + 1
Loop

rs.Close
Set rs = Nothing

End Sub

I am trying to pull the last names from a query and have them listed in the
combo box as the form loads. The combo box is unbound, but originally its
rowsource was from the query itself. I was getting strange problems with the
wrong records being deleted or appended. I have been programming VB for about
a year but I am only just begining with Access and databases in general.

My form has 3 combo boxes; Last Name, First Name, and Supervisor. Last Name
will have an afterupdate event that will pull all the first names from a
query that have the selected last name. First name will do the same for the
supervisor. So If there are 3 people named Smith and only one of them is
Steve, Steve Smiths supervisor will appear in the appropriate combo box once
Steve is selected from the combo box.

Once I get that, my next step will be to find the record in the Table and
delete it. My problem is understanding how the database objects work. I have
printed out a lot of online resources that I am going to read tonight. But if
anyone has a suggestion I would appriciate the help.