docmd.openform and openargs making me crazy

[RuralGuy]

Setting Response = acDataErrAdded or acDataErrContinue
is what tells the ComboBox to requery its RowSource or not.

 

[mollycase]

okay, update...the string is getting back to the main form.
but it dings at me and gives me the error msg, i have to reenter the name in the main form or select it from the drop down box.

so it works just fine, i just need to get rid of it dinging at me after i enter the info in the popup and close that.

searching for that answer....

 

[mollycase]

oh, ok, RG. i just saw your post - i hadn't refreshed.
i'll see what i can figure out!

 

[RuralGuy]

Did you see my last post yet about the Response variable?

 

[mollycase]

rabbie -
cool, thanks!

 

[mollycase]

here's my code for the NotInList event: is the acDataErrAdded in the wrong spot? or do i need to put in two places?

Private Sub cboAuthor_NotInList(NewData As String, Response As Integer)
On Error GoTo cboAuthor_NotInList_Err

If MsgBox("The author " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Appell Library") = vbYes Then

DoCmd.OpenForm "frmAuthorEntry", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
MsgBox "Please choose an author from the list." _
, vbInformation, "Appell Library"
Response = acDataErrContinue
End If
cboAuthor_NotInList_Exit:
Exit Sub
cboAuthor_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboAuthor_NotInList_Exit
End Sub

 

[RuralGuy]

It looks exactly right and is *much* easier to read when indented and code tags are used.

Private Sub cboAuthor_NotInList(NewData As String, Response As Integer)
   On Error GoTo cboAuthor_NotInList_Err

   If MsgBox("The author " & Chr(34) & NewData & _
             Chr(34) & " is not currently listed." & vbCrLf & _
             "Would you like to add it to the list now?" _
             , vbQuestion + vbYesNo, "Appell Library") = vbYes Then

      DoCmd.OpenForm "frmAuthorEntry", , , , acFormAdd, acDialog, NewData
      Response = acDataErrAdded
   Else
      MsgBox "Please choose an author from the list." _
             , vbInformation, "Appell Library"
      Response = acDataErrContinue
   End If
cboAuthor_NotInList_Exit:
   Exit Sub
cboAuthor_NotInList_Err:
   MsgBox Err.Description, vbCritical, "Error"
   Resume cboAuthor_NotInList_Exit
End Sub

Is it still not working correctly?

 

[mollycase]

hmm, i think this is one of those times where i'm missing something basic but critical, kind of like when your car won't start and then you realize it's not in park?

i've attached my db with the code from you RG that i've been fooling with.

i think i have it right. i went back and spaced/indented, all my cut/paste messed that up

what are code tags? is that a space/indent convention to know what is where?

i just tried it again. what happens is

frmBookInfo: enter new author name like this "LastNew, FirstNew"
frmAuthorEntry: pops up with fields filled correctly
close popup formkj
frmBookInfo: dings at me giving me the generic Access error msg. I can either start retyping the new entry which will pull it up or scroll down the box. it IS listed in there, but i don't know how to get the generic error msg from popping up .

i feel so close to getting this!!!!!!!!!!!

 

[mollycase]

hi - i just wanted to add that except for the authors notinlist event, all my other notinlist events work just fine. the new string is added, it tells me so, and that is that.

authors is the only one where i have to have a form open also, so maybe that has something to do with it. do i need an afterupdate event or something? from what RG has said, the response=acdataerradded should take care of all that.

hmmmmm..

thanks for any help -
molly

 

[vinzz]

If you only want to add the text of the combo in a table, use a sql string:

example that adds 2 values:

Private Sub Something_NotInList(NewData As String, Response As Integer)
Dim db As Database
    Dim LSQL As String
    Dim LResponse As Integer
    Dim ctl As Control
    Dim Medid As Long
    
    On Error GoTo Err_Execute
    Set ctl = Me!somebox2
    Medid = Me!somebox
Set db = CurrentDb()
       LSQL = "insert into Sometable (somecolumn,somecolumn2) values ('" & Medid & "','" & NewData & "')"
        db.Execute LSQL, dbFailOnError
        Set db = Nothing
        Response = acDataErrAdded
    On Error GoTo 0
    Exit Sub

Err_Execute:
        Response = acDataErrContinue
    ctl.Undo
    MsgBox "Action failed"
End Sub

 

[mollycase]

i give up!!

okay, so i've been trying all sorts of things and i just cannot get this to work. my code is below.

What currently works:

If user tries to enter an author that is not in the list, the not in list event is triggered. This opens popup form, frmAuthorEntry. The fields are already populated due to passed args. There is a command button to close the popup and return to the main form.

What does not work:
Once I close the popup, the access error message says the item i'm trying to add isn't in the list. BUT IT IS. I just have to re-select or re-type it.

I have been stumped on this for a very long time now.

Every single other combo box notinlist event works for me and I cannot for the life of me figure out why this is not working.

The cboAuthor is based on a query that concatenates the the first and last name.

If i start putting things in that are supposed to requery, i get a msg error 2118, save before requery. even if i add the save button and then close the form with the X in the corner, it STILL does the whole error msg thing about not in the list until i re-type or select from drop down. but it's supposed to already requery based on the response=acDataErrAdded thing. arrggghhhhhhh!!!!!!!!!!!!!!!!!

nothing i try has worked. i've tried docmd stuff to save or close, and that doesn't work, it tells me the popup is empty, [frmAuthorEntry=Empty]
i have NO idea what that means.

this is the only thing preventing me from finishing this database.

Here is my code for NotInList:

Private Sub cboAuthor_NotInList(NewData As String, Response As Integer)
On Error GoTo cboAuthor_NotInList_Err

If MsgBox("The author " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo + vbDefaultButton1, "Appell Library") = vbYes Then

DoCmd.OpenForm "frmAuthorEntry", , , , acFormAdd, acDialog, NewData

Response = acDataErrAdded
Else
MsgBox "Please choose an author from the list." _
, vbInformation, "Appell Library"
Response = acDataErrContinue
End If
cboAuthor_NotInList_Exit:
Exit Sub
cboAuthor_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboAuthor_NotInList_Exit
End Sub

-----------------

Code for the popup form OnLoad



Private Sub Form_Load()
Dim Args As Variant
If Not IsNull(Me.OpenArgs) Then
Args = Split(Me.OpenArgs, ",")
Me.AuthorLastName = Args(0)
Me.AuthorFirstName = Args(1)
End If
End Sub

 

[mollycase]

vinzz - thanks, but what i have is working for me in that respect. i can add the data to the combo just fine, but i can't get it to act like the other notinlist events, meaning, after adding the new data, it says "ok great" and not "it's not in the list", making me have to reselect it.

i appreciate your post, though.
mollycase

 

[unclejoe]

Molly,

It appears that the line to requery your combo is missing in your code.

        DoCmd.OpenForm "frmAuthorEntry", , , , acFormAdd, acDialog, NewData
      
        Response = acDataErrAdded
     [B]me.cboAuthor.requery  ' add line here[/B]
    Else
        MsgBox "Please choose an author from the list." _
        , vbInformation, "Appell Library"
        Response = acDataErrContinue
  End If
cboAuthor_NotInList_Exit:
  Exit Sub
cboAuthor_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboAuthor_NotInList_Exit
End Sub

Because your current combobox records refers to the older set of data.

 

[mollycase]

uncle joe - thanks, i've tried that too. i just retried it to make sure.
after i hit the button on the popup form to close the popup, it closes the popup, then it tells me i need to save the current field before the requery action and gives me the standard access error message.
that's where i am getting so confused as well. i don't understand how to save the record. i've read that access automatically saves when you advance to a new record, but i've tried that too and still get the same thing.
the new data is ALWAYS added to the combo box, though. that was the easy part.

the hard part is trying to get access to see that it is there without having to reselect it.

 

[RuralGuy]

I'm sorry Molly but after playing with this for several day, you have me stumped as well. I do not have a good answer for you. If I come up with something I'll post here.

 

[mollycase]

oh man, i must be in trouble if you can't figure it out!

if i figure it out somehow, even thru some convoluted way, i'll post back too.

thanks for your help, rg!

 

[boblarson]

I have been playing around with it too and I think it comes down to the fact that what you are saving into the table isn't what was in the openargs. You are splitting it and putting something completely different in there, which then Access can't see as being added because it wasn't what it sent your form.

 

[unclejoe]

Hi Molly,

I have resolved this form "frmAuthorEntry" for the onload event

Private Sub Form_Load()
   'Dim Args As Variant
   Dim Args() As String
   If Not IsNull(Me.OpenArgs) Then
      Args() = Split(Me.OpenArgs, ",")
      Me.AuthorLastName = Args(0)
      Me.AuthorFirstName = Args(1)
   End If
   Form_frmBookInfo.cboAuthor = ""
End Sub

Note that I am using this on version 2000.

I'm still having problem with the "cboAuthor" combobox. One main problem is that this combobox is bound to the AuthorID, it will not work in this case as the name of the author is not the ID and also you'll have problems with the combobox due to one problem.... the name with space and a comma. It's impossible for the combobox to requery again.

If you key in the combo like "Tom,Jones", then to popup appears nicely the the forms fields.

If you key in the combo like "Tom, Jones", then the field in your form "last name" will have a space like " Jones".

uncle joe - thanks, i've tried that too. i just retried it to make sure.
after i hit the button on the popup form to close the popup, it closes the popup, then it tells me i need to save the current field before the requery action and gives me the standard access error message.
that's where i am getting so confused as well. i don't understand how to save the record. i've read that access automatically saves when you advance to a new record, but i've tried that too and still get the same thing.
the new data is ALWAYS added to the combo box, though. that was the easy part.

the hard part is trying to get access to see that it is there without having to reselect it.

 

[mollycase]

thanks bob and uncle joe!

so what i'm seeing then is that perhaps i need to requery the underlying table for authors instead of the combo box? i think i've read about that somewhere in my searches.

i originally had my the "," set up with no space, but of course it gave me " Jones" so I changed it to ", " to account for that. perhaps that was not good.

i'm not really sure what else to do at this point except to just deal with the weird error and go about entering the books until i can figure it out.

again, i appreciate your help!

molly

 

[mollycase]

hi there - i've gotten a version of db working so i can enter data and use it while i make it better in a different version.

i've been looking around to try to solve my cboAuthor problem. Bob said that the data i'm passing back is not the same as the data that went out.

i found some advice saying that after requerying the cbo so that the new author is in there, that i should do something like setting the value of the cbo to the concatenated string.

before i go off down headache alley with this one, i'd thought i'd post back.

does this seem like something that would work? i've found that what i think will work and what DOES work are often very different with access

thanks
molly