Cbo Update Problems

[Guest]

Hello all! I've read a couple of hundred posts pertaining to my dilemma, but
keep falling short of getting it all to work together. I have a subform,
PartsUsed, inside of my DispositionEntries form. It contains a Cbo called
PartNumber with 2 columns, but displaying only the (0) column. The second
column populates the second field using the AfterUpdate event of the first
field, (Me.Description=Me.PartNumber.Column(1)
There will be frequent parts added and so I used the NotInList event of the
PartNumber field to open up a new dialog, AddPartNumber, which I then add my
"Description" info into and save and close. This does bring me back to my
original form, but it then asks if I want to add the part number again. I've
tried a couple of requery attempts, i.e.,
Forms!MainFormName!SubFormName!ComboBoxName.Requery,
in the OnClose event of my dialog box which I got from another post, but it
refuses, telling me I must save the current field first. I saved it every
way I could think of, but keep receiving the same error. The data is being
saved correctly to my lookup table, but not to my PartsUsed table. Here is
my NotInList code:

Private Sub PartNumber_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Label
Dim strMessage As String
Dim strFormName As String
DoCmd.Save
strMessage = "Part Number: '" & UCase$(NewData) & "' is not in the list. " &
vbCrLf & "Would you like to add it?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Add New Part") = vbYes Then
strFormName = "AddPartNumber"
DoCmd.OpenForm strFormName, acNormal, , , acFormAdd, acDialog, UCase$(NewData)
Response = acDataErrAdd
Else
Response = acDataErrContinue
End If

Exit_Label:
Exit Sub
Err_Label:
MsgBox Err.Description
Resume Exit_Label

End Sub

This came from a post here also, but note that the Else statement does not
work and what I am actually using I had to remove the Else, End If and
Response = acDataErrAdd. I don't really understand some of the functionality
of the responses, but it was the only way I could get it to work at all. I
noted in a post that I was not alone in this, but there was no solution
forthcoming in that one.
Any ideas on how to get me off the horns of my dilemma??!! Thanks in advance!

 

[Guest]

Bryan,

I had the same problem and finally found the answer by searching the posts.

I placed this code in the On Not In List event of the control:

Dim strMessage As String
Dim strFormName As String
strMessage = "The name '" & UCase$(NewData) & "' is not in the list.
" _
& vbCrLf & "Would you like to add the name and phone number?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Add New Name") = vbYes
Then
strFormName = "NewReqByFrm"
DoCmd.OpenForm strFormName, acNormal, , , acFormAdd, acDialog
UCase$ (NewData)
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

I think I am where you are in understanding code but I was able to replace
the example names with my field names and it works. This gives a custom
message saying the newdata is not in the list, do I want to add it. Since I
need to add a couple of fields, it opens a form to do so.

After I enter the new data, on my close buttons On Click event I placed:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.CLOSE


This seems to refresh the data and it shows immediately in the list upon
return to the original form.

Those wiser than me can say if I have it right or if there is a better/more
efficient way of doing this. I was just glad it worked.

hth

Gary

 

[Guest]

Gary,

Thanks for the reply. My code was identical to yours with the exception
that I inadvertently typed acDataErrAdd instead of Added in my post. I had
it correct in my code, yet it kept telling me that the Else was wrong. Then
it balked at the End If. I'll try your close event shortly.

 

[Guest]

Gary,

I just got to insert your Close code and it worked perfectly! Thanks! I
still need to solve the "Else" issue and understand the two responses in the
NotInList event code and I'll be in hog heaven!
Thanks again!

 

[Guest]

All,
I still need some help in understanding the Responses. I am not sure if the
problem I am experiencing is caused by this or not. Basically, it will not
take no for an answer. I get "The action or method requires a Form Name
argument." when deciding not to add the part. I still cannot get the Else
statement to work and have tried and tried. I've been through all of the
help and cannot find anything wrong with my code, but it insists that I can't
have my Else without an If. I have no idea where it wants me to stick
another If and don't see another one in any example. Any ideas?

 

[Guest]

Never mind! I got it thanks to Fredq on another post. I had inadvertently
pulled my strFormName = "" up on the same line as my Then. I now have
reponses and it now takes no for an answer!