combo box selection doesn't display associated record

[Rooski]

I am new to Access but have a developer's background on mainframes. After researching several FAQ's and threads in this forum and not finding an answer, I decided to go public with my problem.

I am using Access 2000. I created a form that is made up of a series of fields from one table. In an attempt to populate the fields on this form based on the selection of a value in the key field for this table (an Autonumber project ID), I created a combo box using the wizard.

When I went into form view, the first record in the table was displayed perfectly. When I clicked on the combo box, it listed all the project ID's perfectly. However, when I selected another ID from the combo box list to display a different record, the record didn't change. The form continued to display the first record in the table.

The wizard created the following code for the combo box:

Private Sub Combo76_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProjectNumber] = " & Str(Me![Combo76])
Me.Bookmark = rs.Bookmark
End Sub

The combo box is unbound. The following data property entries were generated by the wizard based on the options I chose during the process:

Row Source Type = Table/Query.
Row Source = SELECT [ProjectMain].[ProjectNumber] FROM [ProjectMain].
Bound Column = 1.
Limit to List = No.
Auto Expand = Yes.
Enabled = Yes.
Locked = No.
Every other data property is blank.

Any ideas as to what might be wrong?

Thanks, Rooski

 

[missinglinq]

Since it only takes about a minute to run the ComboBox Wizard, I'd delete your combobox and make a new one. Access is so complex that all kinds of idiosyncratic glitches occur. If the same thing happens I suspect you've got corruption of some sort.

Good Luck!

The missinglinq

There's ALWAYS more than one way to skin a cat!

 

[Rooski]

missinglinq,

Thank you for the quick response.

I deleted the combo box as suggested and then created a new one. Unfortunately, that did not resolve the problem. But that was a good idea.

Thanks, Rooski

 

[dk87]

Private Sub Combo76_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProjectNumber] = " & Str(Me![Combo76])
Me.Bookmark = rs.Bookmark
Me.Refresh
End Sub

 

[missinglinq]

Does your form have the usual record navigation controls, and if so, does clicking on them go thru the records properly? If the controls are present, but using them also fails to produce the additional records, then perhaps the problem isn't with the combobox but rather with the underlying table/query is corrupt.

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya Rooski . . . . .

[blue][purple]Change:[/purple]
   rs.FindFirst "[ProjectNumber] = " & Str(Me![Combo76])
[purple]To:[/purple]
    rs.FindFirst "[ProjectNumber] = " & Me![Combo76][/blue]

See Ya! . . . . . .

 

[Rooski]

missinglinq, dk87, and AceMan1,

I just got in the office and found your responses waiting. Thanks to each of you.

To answer missinglinq first, my form is equipped with the record navigation controls and I can view the records using the controls. I only have two test records in the table for simplicity's sake.

dk87 and AceMan1,

I made the suggested changes each of you recommended and ran them separately. Neither corrected the problem. I then decided to mix and match your individual changes and that still didn't correct the problem.

The only other information I can think of adding is that I reset the autonum ProjectNumber to zero a couple of times after deleting some unwanted test records over the last several days. Since the last time I did that, I have deleted another 5 test records. Consequently, I am on ProjectNumbers (autonum) 6 and 7 but, of course, the record navigation controls display those as records 1 and 2. But, as I indicated to missinglinq, the records display just fine using the record navigation controls.

Thanks, Rooski

 

[TheAceMan1]

[blue] In an attempt to [purple]populate the fields[/purple] on this form [purple]based on the selection of a value in the key field[/purple] for this table (an Autonumber project ID), [purple]I created a combo box[/purple] using the wizard.[/blue]

Later you said:

[blue]However, [purple]when I selected another ID[/purple] from the combo box list to display a different record, [purple]the record didn't change.[/purple][/blue]

Is it your intent to [blue]populate from a table[/blue] or [blue]lookup existing records in the form?[/blue]
If its your intent to [blue]populate[/blue] the code for the combobox is all wrong (as its for lookup).

See Ya! . . . . . .

 

[Rooski]

AceMan1,

I'm not sure what you mean by "lookup existing records in the form". I believe I am populating this form from a table. The table contains the Project ID as well as all the fields that are on the form.

Just before I received your last message, I made an unusual discovery through experimentation. When I originally created the form, I set the following form properties to "NO"; Allow Edits, Allow Deletions, Allow Additions, Data Entry. I did this because I only want the form to display the information once it is retrieved based on the value selected in the combo box. I didn't want users changing the displayed information.

The experiment was this. I change only the value for Allow Edits from "NO" to "YES". When I went back to form view, the combo box was working perfectly!! I have no idea why this is so and I'm not sure whether it's kosher or not based on your last question. Any input you still might have would be appreciated.

Thanks, Rooski

 

[PHV]

When the AllowEdits property is False the Combo value don't change ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rooski]

PH and AceMan1,

PH, yes, that does make sense. And AceMan, I now know what you mean by your question. Those options are in the Combo box wizard. I chose "Find a record on my form based on the value I selected in my combo box".

Another question, how can I limit the values in the combo box so only project ID's for projects that have an "open" status are displayed? The Project Status field is in the same table as the Project ID.

Thanks, Rooski

 

[PHV]

Combo76.RowSource = "SELECT ProjectNumber FROM ProjectMain WHERE Status='Open'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rooski]

PH,

Thanks very much. That worked very well.

Regards, Rooski