docmd.openform and openargs making me crazy

[mollycase]

ok, here goes.

i have a combo box on a form called cboAuthor. in the form, on the row source line, i used the ... to create a query that will concatenate the first name and last name of the authors and sort alphabetically. that all works just fine.

however, i am trying to do the NotInLIst thing to be able to add authors. after some researching, it seemed like i would need to use the docmd.openform procedure to open up the form that enters into tblAuthor.

i can get it to open up the form, but i cannot get it to pass the value nor can i get it to record the value once entered. i am pulling my hair out.

i have tried the code on applecore, a bunch of different things here and on other sites. i just can't figure it out.

i saw about how in the OnLoad event of the second form, i will need to use the split function to sort out the first name and last name (it would be entered as LastName, FirstName in the combo box).

i just don't get it.


i see things for passing more than one variable, but i can't get it to work for me, because i am not passing more than one named field. in the combo box, it's all together, not in separate fields. so i am confused.

any suggestions? or clarity?

i've got three books and the web and my brain has given up.

 

[gemma-the-husky]

the first catch is that after you open the entry form, you need to wait for it to return, and only then requery the combo box - otherwise you dont get any newdata in there!

so

docmd.openform("myform")
while isopen("myform") 'this will come later
doevents
wend
cbobox.requery

now, while your "myform" is open, add new records etc to the underlying data, as necessary. you can get smart, and "save" the value of the new item, and use this as the new value for combo box, but leave that a moment

-------
now isopen ...
uses a method of the syscmd object

Function IsOpen(strName As String, Optional objtype As Integer = acForm)
IsOpen = (SysCmd(acSysCmdGetObjectState, objtype, strName) <> 0)
End Function

note that you can use this for other objects

eg isopen("myquery",acquery)

--------
hope this helps

 

[RuralGuy]

How about just using the acDialog argument of the OpenForm? That will freeze the code of the first form until the second form is closed it made invisible.

 

[mollycase]

my code

thank guys, but gemma - you lost me. and rg - i think i am using code you suggested to someone else.

here is my code:

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, "Library") = vbYes Then

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

it will bring up the frmAuthorEntry, but nothing passes over. and if i type something in, the form doesn't close or take the data back to the other form.
then when i delete what's in the combo box and try to switch views or close the form, i get this weird error about how it can't find a certain field in another table unrelated to the author (subcategory, i have no idea what the deal is with that.)

cboAuthor is the name of the combo box, the control is AuthorID. should i be listing AuthorID instead of cboAuthor in the above code? i'm so confused that i can't just play with it anymore, because i have no idea what's going on anymore. it seems like this should be simple, but it's not.

then there's this code for the OnLoad in the opening form:
_______________________________
Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
Me.cboAuthor = Me.OpenArgs
End If

End Sub
____________________________

I know that Me refers to the current form, but I don't understand how to get the string from the main form (LastName, FirstName) to the second form where the fields are AuthorID, AuthorLastName, AuthorFirstName.

i tried some split function stuff and i just keep getting all different error messages. i've been playing with this all day and just can't get it right. i've been looking up in Help for VB and Access, this forum, the web, three books. It's got to be something simple I'm just not aware of. I hope.

 

[RuralGuy]

The code does look like something I would do but I have some questions. Are both forms bound to a query of the same table? Why are you putting the passed data in a ComboBox? I would think it would go in a TextBox on the 2nd form. What data has been typed in the ComboBox of the 1st form that invokes the NotInList event? That is all that is currently being passed. Can you post a picture of your form so we can see what you are dealing with? There are methods to get the other fields from the 1st form but it sounds like something is not right.

 

[mollycase]

here is the link for where i got the code.
http://www.access-programmers.co.uk/forums/showthread.php?t=135415

In the OnLoad event, what is the Me. to refer to?

My second form looks like this

LastName __________________
FirstName__________________

The AuthorID is an Autonumber.

When I've tried using the split function on the second form, it didn't matter because nothing was getting passed to begin with.

In the main form, there is this:

Title
Author
Category
Subcategory

Author is based on a query that is made within the row source property. I saw somewhere that I should do that (I think Martin Green's site) rather than making a query outside the form that the row source is based on.

The query concatenates the first and last names, placing them in alphabetical order by last name this way: LastName, FirstName

If someone types in name that is not in the combo box already, that triggers the NotInList event.

The second form pops up, but no strings are passed.

 

[RuralGuy]

In the OnLoad event, what is the Me. to refer to?

Me. refers to the current form which in this case is your 2nd form.

The query concatenates the first and last names, placing them in alphabetical order by last name this way: LastName, FirstName

...so in this case can I assume that you have passed a string equal to a new "LastName, FirstName"? If so then you will can use the Split() function to separate them.

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]

when i use that second bit of code, which i tried before, it tells me the item is not in the list. it is a text box, not a combo box or list box, i just double checked.

i'm really confused.

i'm attaching my db, maybe that would help.

crap, i just accidentally deleted the main form.

that sucks. i'll have to redo the main form and then repost.

well, it wasn't that complicated.

is there a different way i can add the author names thru the main form? i'd like to do it the right way, but this is for a friend and i'd really like to just get it done soon. it's making me frustrated that every time i think i have something figured out, something else pops up that takes me a week to figure out!

thanks for trying to help!

 

[boblarson]

when i use that second bit of code, which i tried before, it tells me the item is not in the list. it is a text box, not a combo box or list box, i just double checked.

i'm really confused.

i'm attaching my db, maybe that would help.

crap, i just accidentally deleted the main form.

that sucks. i'll have to redo the main form and then repost.

well, it wasn't that complicated.

is there a different way i can add the author names thru the main form? i'd like to do it the right way, but this is for a friend and i'd really like to just get it done soon. it's making me frustrated that every time i think i have something figured out, something else pops up that takes me a week to figure out!

thanks for trying to help!

It might be faster if you post the db. See here for how:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[mollycase]

i have to remake the main form and then i will. thanks!

 

[DCrake]

Simple Software Solutions

Molly

Please find attached a working copy of your solution.

Let me know if it fits the bill.

CodeMaster:

 

[mollycase]

thanks but i was unable to open it.

 

[RuralGuy]

The attachment is in ac2k format and I was able to open it just fine.

 

[mollycase]

ah, i see.
i'm at my friend's house today working on the database.
they do not have wireless internet, so i have to use their computer to get online. their version of access is 97. i'll have to see if i can get my laptop to hook up to their internet access.

thanks rg and dcrake!

 

[RuralGuy]

You should be able to. Post back if you need some help getting on the internet at your friends house.

 

[mollycase]

ok, i got it open! looks like the example i needed to understand this thing.

thanks for the offer RG, but i used to do tier 1 tech support at a cable company, so troubleshooting internet connections is something i CAN do lol

i'm going to go check this out. i'll post back later with how it worked out and a copy of my db as well.

thanks again to both of you - i really appreciate your help!
molly

 

[mollycase]

feeling less crazy

okay, i have a few questions (of course).

1) In VB (or the MS version of it), does capitalization not matter?
- in the Form_FrmEmployees NotInList event, the private sub is called CboEmployees and then it the on error says "cboemployee". That is read the same way by Access/VB?

2) In the AfterUpdate event on the FrmEmployees...is that pulling the strings/array/whatever from the OnLoad event of the frmEmployeeEntry?

So this code is basically taking a string, let's say Emp Name, and doing this with it:

frm_Employees: Emp Name - nope not here
opens frm_EmployeeEntry: First Name: Emp, Last Name: Name
then sends that same info back to the frm_Employees where the AfterUpdate tells it what to do with which strings (the "Me.Txt" stuff) and it gets inserted into the underlying table?

pardon my reducto absurdum (sp) here, but even tho i'm doing this for fun(and maybe because of that), i'd like to make sure i know what's going on so i can play with it in the future.

also, is there a way to save what i have in the VB window? since i keep making changes to the code, i was wondering if there was a way to be able to view the different permutations i've gone through to make it easier to figure out what i was doing when a certain thing happened? i guess i could print it out, but is there another way?

thanks
molly

 

[Rabbie]

also, is there a way to save what i have in the VB window? since i keep making changes to the code, i was wondering if there was a way to be able to view the different permutations i've gone through to make it easier to figure out what i was doing when a certain thing happened? i guess i could print it out, but is there another way?

thanks
molly

If you look in the File menu of the VBA editor you should find an Export option. this will let you save your current version. There is also an Import function so you can restore it if necessary.

 

[RuralGuy]

Hi Molly,
To answer another one of your questions: Access is not Case sensitive unless you take extra steps beyond the defaults.

 

[mollycase]

okay, using the original code i had (posted above) for the NotInList event for cboAuthor and the OnLoad code from RG that uses the split(), i can get the string passed and separated like it should be.

now i just need to get the popup to close and go back to the main form and have the new author be in the list.

i know i saw some threads about that, so i'm going to check it out.

thanks,
molly