Combo refresh problem 1

[nq]

I have a main form with a unbound combo box (FindID). A user can select a person from the combo list and retieve the record. Limit to List is Yes. Row Source:
SELECT tblTeacher.ID, tblTeacher.Surname, tblTeacher.Given, tblTeacher.Email FROM tblTeacher ORDER BY [Surname], [Given];

If the person is not in the list, there is an option button to call a pop-up form to add the new persons's details. The pop-up is then closed. On the main form, I have the following code attached to the "FindID" combo box:

Private Sub FindID_GotFocus()
Me.FindID.Requery
End Sub

When I click on the combo box (Got Focus) and type in or scroll, to select the newly entered name, the combo box finds the new name in the list using the following code:

Private Sub FindID_AfterUpdate()
Me.RecordsetClone.FindFirst "ID = " & Me!FindID
If Me.RecordsetClone.NoMatch Then
MsgBox Me!FindID & "Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

However, it also returns an error message saying "534 Not Found" 534 is the ID of the new record. The new name has been added to the list. Examining the table, the new record is present - hence the new autonumber ID (534 in this case) but the form will not retieve the record.

If I exit the form and come back in, the new record is present and works correctly.

 

[TheAceMan1]

How are ya nq . . .

I don't see how your able to do this with the [blue]Limit to List[/blue] property set to [purple]Yes[/purple]. You should get the following (limit to list generated) message as long as the data is not in the list . . .

[blue]The text you entered isn't an item in the list.

Select an item from the list, or enter text that matches one of the listed items.[/blue]

. . . [purple]and this should prevent you from selecting your option button![/purple] Everything makes sense if [blue]Limit To List[/blue] is set to [purple]No[/purple].

The usual way to handle this is thru the [blue]NotInList[/blue] event (put the cursor on the event line and hit F1)

In any case, you've added a record to the table and updated the combobox, but [purple]you havn't updated the [blue]RecordSource[/blue] of the form![/purple] You need to requery the form!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[nq]

Hmmm...
Yes and No. The combo box has been updated with the information because I can select the new name from the displayed list. The problem is that when:
Me.RecordsetClone.FindFirst "ID = " & Me!FindID
tries to retrieve the record, it can't and the "534 Not Found" message is displayed. So the combo box thinks the record is there but the rest of the form doesn't.

Just to lead you astray, I tried to set LimitToList to No and a message popped up:

"...can't set the LimitToList property to No right now.
The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column.
Adjust the ColumnWidths property first and then set the LimiToList property."

This statement is true but is what I require. When selecting a Surname (which may not be unique)from the combo list, the bound column must be the ID of that person (to retrieve that specific record) but I don't want to display the ID in the box, I want to display the Surname, hence I set the ID width to zero.

Back to the subject at hand again: I performed a form refresh on the pop-up form as well as the combo requery on the main form and everything works well. Thank you for your suggestion.

However, when I first tried this, I used the following code on the Pop-up CloseForm:

Forms![frmTeacher].Requery
DoCmd.Close

This promply closed the main form (frmTeacher) and left the pop-up form open!! Somehow the close cmd became associated with the requery command. This is not logical to me. Is there something I should know?

I changed the order to:

DoCmd.Close
Forms![frmTeacher].Requery

and it works ok.

This is not logical either, once the close command is issued, Access seems to still process commands until the End Sub. Maybe it's because I live in Australia and everything is downside up

Now I can leave the LimitToList set to Yes as required.

 

[TheAceMan1]

[blue]Great Day in the Mournin![/blue]

I always somehow manage to forget the brothers & sisters [blue]over seas![/blue] Sorry about that [blue]nq[/blue]!

I'll try to stay awake more . . .

See Ya! . . . . . .

 

[nq]

G'Day.

And thanks for showing me the "NotInList" event. I don't remember seeing it before but I can see uses for it to make user operation more satisying.

Thanks again.