locking up in Dialog mode???

[daniels012]

Is there any way I can do this code without putting the form in Dialog when it opens??? Everytime Im in Dialog mode my computer freezes (Any computer/ work,home, my laptop)??

Please any help? I can send the file if needed.


Private Sub ProductID_NotInList(NewData As String, Response As Integer)
If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "AddNewProductsFrm", acNormal, , , acFormAdd, acDialog, NewData
Me.ProductID.RowSource = Me.ProductID.RowSource
Response = acDataErrContinue
Else
Response = acDataErrAdded
ProductID.Undo
DoCmd.Save
ProductID.Requery
End If
End Sub


Thank You,
Michael

 

[GingerR]

Not sure what you're doing opening a form. Not sure what

Me.ProductID.RowSource = Me.ProductID.RowSource

is for.
Plus looks like you are trying to add a record, but set Response to Continue; then when you are undoing, you set Response to Add?

Use this code for NotInList event (tweak as necessary: substitute Table "Parts" with your table name; Field name "Part" with your field name) You have to make sure a reference to DAO is set as well:

Dim ctl As Control
    
    ' Return Control object that points to combo box.
    Set ctl = Me!Part
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("Parts")
        rs.AddNew
        rs!Part = NewData
        rs.Update
        Set rs = Nothing
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[daniels012]

Sorry, I am new to coding. Let me try to explain...

I am using an order form. If we have a new item to add to our products I have this code come up and ask a question if I want to add a new item. If yes it opens the form for entering the new item (AddNewProductsFrm). I enter the new product and all the info that applies, then it should take me back to my combo box and allow me to enter the new item.

Hope this clears up what I am trying to accomplish,
Michael

 

[daniels012]

How do you get your code to show up in a special window?

 

[GingerR]

TGML code = click below on "Process TGML"

I see what you are trying to do above. In my code, it adds the new value right to the table for you, so you don't have to do it yourself. You never leave the current form. But I guess you have to add additional info. You could use my code, then open the form to the new product ID.

What's the rowsource of your combo box, and what is the bound field?

In any case, your responses are backwards; you don't need to open in Dialog mode; and I'm still not sure what you're doing with setting the form's recordsource to itself. Try this instead:

Sub ProductID_NotInList(NewData As String, Response As Integer)
    ' Prompt user to verify they wish to add new value.
    If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
    vbQuestion + vbYesNo) = vbYes Then
        ' Set Response argument to indicate that data is being added.
        Response = acDataErrAdded
        'Open form in ADD mode
    DoCmd.OpenForm "AddNewProductsFrm", acNormal, , , acFormAdd
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
        Response = acDataErrContinue
        Me.ProductID.Undo
    End If

End Sub

Then in your combo box's OnEnter or OnClick event, put me.ProductID.requery

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[daniels012]

I typed in the code exactly as you described.
When I hit yes on my message box, my form opens and I get message that says "The text you entered isn't in the list".
So I kit OK. I enter my new item and close the form. It takes me back to my order form in the combo box of the subform (where I started)and will not accept my new item.

Help!!

Michael

 

[GingerR]

not accept my new item" what does that mean?

did the thing get added to the table?
If you open the form again, is the new thing showing up in the combo box?

If YES and YES, put in the combo box's OnEnter event Me.ComboBoxName.requery (sub for comboboxname)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

And this ?
Private Sub ProductID_NotInList(NewData As String, Response As Integer)
If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "AddNewProductsFrm", acNormal, , , acFormAdd, acDialog, NewData
Me.ProductID.Requery
Response = acDataErrAdded
Else
Response = acDataErrContinue
ProductID.Undo
Me.ProductID.RowSource = Me.ProductID.RowSource
End If
End Sub

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

 

[daniels012]

YES, when I close the form and reopen it, it is available. I did enter in the combo box's OnEnter event Me.ProductID.requery

It still will not show up?? It just isn't requerying
I do appreciate your help though, I know I am new to coding...
Any Ideas?
Michael

 

[GingerR]

then try the OnClick event

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[daniels012]

Tried it! No luck?

Not sure what I'm missing?

Michael

 

[daniels012]

Is there another event I could try??

michael

 

[GingerR]

hm...the issue is that you're adding the item to the list, but i think when you return to the main form you are already 'in' the Enter and Click events, so they are not firing again. I'll play around with it a little and figure something out.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Just to know michael, have you tried to play with my suggestion ?

 

[daniels012]

Yes PHV I did try your method. Any time I open a form in Dialog, Microsoft Access locks. Even on different computers. So I have to use a different method than Dialog. I am not sure why it is not working properly. I don't know how to attach a copy of the form, but I have no problem letting someone else look at it.

I appreciate the time you both are giving this. I wish I wasn't so "green" to coding.
If there are any other ideas out there? I am open to them.

Michael

 

[PHV]

Any time I open a form in Dialog, Microsoft Access locks
Even in brand new database ?

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

 

[daniels012]

I want to say yes, because I have another form that has a NotInList I could not use Dialog. I have a very strange work around to make that form work.

Michael

 

[GingerR]

you don't have to use acDialog. why dialog? I don't think it was locking because you're opening in Dialog mode anyhow. I think it had something to do with your code.

I tried your way too PHV. Seems logical but it doesn't work, just like none of my suggestions work (Click or Enter). I think it's because the cursor is in the combo box, it won't requery right then since it's 'active'. maybe set focus to a different control, then set it back to the combo box....

Ok try this:

    If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
    vbQuestion + vbYesNo) = vbYes Then
        Response = acDataErrAdded
        Me.ProductID = Me.ProductID.OldValue

I change the combo box back to it's original value first, then no errors like 'you must save the record before requerying' and stuff like that.

Then put requery in the OnClose of the AddNewProductsForm.

I tested and it works.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[daniels012]

I can tell we are close!!!

I get this error when I enter
Me.ProductID = Me.ProductID.OldValue
"You tried to assign Null value to a variable that is not a Variant Type"

Any Ideas?

 

[GingerR]

how about

if isnull(me.productID.oldvalue) then
me.productid = ""
else
me.productid = me.produceid.oldvalue
end if

this would accomodate if the productid was blank to begin with, or had something else already in it but the user wanted to pick something else.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244