Form will not requery no matter what

[justme]

I have a form based on one table. On it, I have a combo box with not_in_list
sub to add the new style number. The combo box requeries ok, but the form
does not and can't find the new number in a search.

I have tried
Me.Requery
Me.Refresh
in combinations with Me.dirty = False or If me.Dirty = True then me.dirty =
false

I tried placing various combinations in several places, such as
in the sub right after the new data is inserted into the table
at the end of the sub
putting in its own sub and calling it.

Any time I place the requery/refresh command in the code ANYWHERE, it messes
up the combo box requery, and then nothing is requeried.

I even put a requery command button using the wizard, and put a msgBox in
the sub. The msgBox comes up, but I still can not find the style in the
form, even though it shows in my combo box drop down. the only way I can get
the form to see it is to close & re-open it.

Do you have any suggestions? I have been searching & trying posts for more
than 2 hours now.

Thanks.



Private Sub cboFindStyle_NotInList(NewData As String, Response As Integer)
'------------------------------------------------------------
' To add New Style - direct insert into tFits
'------------------------------------------------------------
Dim Db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As Object
Dim strMsg1 As String
Dim Result
Dim strMsg2 As String
Dim sqlAddStyle As String

On Error GoTo ErrorHandler

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new StyleNumber.
strMsg1 = " '" & UCase(NewData) & "'" & NL & NL _
& "is not a known StyleNumber. " & NL _
& " Would you like to add it?" & NL & NL & NL _
& " Click No to re-type it" & NL
If MsgBox(strMsg1, vbQuestion + vbYesNo) = vbNo Then
' If the user chooses not to add a StyleNumber, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
Me.Undo
MsgBox NL & NL & " Please re-enter a StyleNumber " &
NL & NL _
& " or press ESC" & NL & NL & NL
Me.cboFindStyle = Me.StyleID
Else
strMsg2 = NL & NL & " Are you sure you wish to add
" & NL & NL _
& " " & Me.cboFindStyle.text & " ?" & NL & NL
& NL
' If the user chose to add a new StyleNumber, open a recordset
' using the tFits table.

If MsgBox(strMsg2, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
' If the user chose not to add a StyleNumber, set the Response
' argument to suppress an error message and undo changes.
' Display a customized message.
Me.Undo
MsgBox NL & NL & " Please re-enter the StyleNumber
" & NL & NL _
& " or press ESC" & NL & NL & NL
Me.cboFindStyle = Me.StyleID

Else
sqlAddStyle = "Insert Into tFits ([StyleID]) values ('" &
UCase(NewData) & "')"
CurrentDb.Execute sqlAddStyle, dbFailOnError
Response = acDataErrAdded

' Look for the StyleNumber the user created in the BuyerAdd form.
Result = DLookup("[StyleID]", "tFits", "[StyleID] ='" & NewData
& "'")

If IsNull(Result) Then
' If the StyleNumber was not created, set the Response
argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
Me.Undo
'Me.Requery
Me.Dirty = False
MsgBox NL & NL & " The StyleNumber was NOT Added." & NL &
NL _
& " Please re-type the StyleNumber or press ESC" &
NL & NL & NL, vbInformation
Response = acDataErrAdded
Else
If Not IsNull(Result) Then
' If the StyleNumber was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End If
End If
End If
ExitHandler:
Exit Sub
ErrorHandler:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub

 

[Douglas J. Steele]

I don't understand what you're describing. "I still can not find the style
in the form, even though it shows in my combo box drop down. the only way I
can get the form to see it is to close & re-open it."

What are you expecting to happen because you've added a new entry to the
combo box?

 

[justme]

Hello Douglas,

I've inserted a new style number to table tFits. I am trying to get the form
to requery the table so that the new style is in the record set of the form
so I can find it.

 

[Douglas J. Steele]

So what's the recordset of the form? Unless it's based on the combo box,
adding a new selection to the combo box will have no impact on it.

 

[justme]

The combo box is based on the primary field of the same table the form is
based on.

1. form and combo both based on table tFits
2. sub for not in list of combo box inserts a new record into the table
3. the combo box requeries the table -- I can see the new style number in
the dropdown
4. I need the form to requery the same table so I can go to the record in
the form

 

[Albert D. Kallal]

The problem is when you use the not-in-list, you are in the middle of a
controls event.....

the normal approach to add data is:

The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. Also, ms-access will
ALSO re-load your form. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.

The problem you have is that the combo box is operating on the CURRENT
form.

However, we can use our new found knowlege of the HUGE number of
things that setting acDataErrContinue does for us.

In your case, you HAVE to add the record INSIDE of this event, since the
AFTER UPDATE EVENT IS going to try and MOVE to this new record.

So, try the following:

If MsgBox("Not found, would you like to add this new reocrd?" & NewData,
vbYesNo) = vbYes Then
Me.RecordsetClone.AddNew
Me.RecordsetClone!Description = NewData
Me.RecordsetClone.Update
Response = acDataErrAdded
End If

note that you have to replace "Description" with the actually column that
your combo box does a search on. Remember, the value of
newdata IS NOT the bound column, but the column that you have for display
(they might be different). Useally you have your combo box return a id
value, but display/search on text values...so, keep this in mind...

 

[justme]

Hi Albert,

Thank you for your reply. I do have several working Not_In_List events that
add a record using a form, and I even have one that uses a direct-add like
this one, the only difference being that the field was not the primary field
for the form. As a matter of fact, the code you just gave me is what it uses,
and also what I originally tried in this current form. I just tried it again,
and I am having the same problem as before:

The form says the style is not known and asks me if I want to add it. I say
yes. It asks if I really want to add it, I say yes. Then it adds the style
number to the table (I verifiable in the table itself), but the form
immediately goes back to telling me the style is not in the list and do I
want to add it. The style does not show in the drop down and the form can
not go to the record in a "find".

If I tell it I want to add it when it asks me the second time, it tries to
add it and errors that it can't add a duplicate value.

What I did was I replaced the 4 lines after "Else" in the middle of my code
with

Me.RecordsetClone.AddNew
Me.RecordsetClone!Description = NewData
Me.RecordsetClone.Update
Response = acDataErrAdded

Like I said, I can't get the form to requery all the records including the
new one, no matter what I've tried, unless the form is closed and opened. Can
you recommend anything else?

 

[Albert D. Kallal]

What I did was I replaced the 4 lines after "Else" in the middle of my

code
with

Well, obviliery,, I could not have by accient gussed your field name?

Is your code really as a above? Was I really a mind reader?

Like I said, I can't get the form to requery all the records including

You don't have to with my example code........

Post your not in list code......what do you have?

I just tested the above....it works fine for me. Of course, you have to
modify the field name in the above to what you are using.

And, not the caution about the combo box returning a id value..but actually
display text...