Combobox based on expression and data entry based on multiple fields

[Fred Boer]

Hi everyone!

I have a data edit/entry form for my school library database (see structures
below). It uses a tab control with three tabs: Title Information, Author,
Subject. When entering a new book in the database, the user enters all of
the title information, then clicks on the Author tab, and enters the
author(s) for the title. The "Author" tab contains a subform for entering an
author. This subform contains a combobox (to look up and insert the author's
name if it already exists in the table), an "Edit Author List" command
button, and three text boxes to display/edit the fields: AuthorLastName,
AuthorMiddleName and AuthorFirstName.

Currently the process works as follows:

If author exists, it is found in the combobox and the data is inserted into
the table in the afterupdate event of the combobox (works fine.)
If author doesn't exist, a "vbYesNo" messagebox pops up and asks if the user
wants to enter the author into the table. If yes, up pops a data entry form.
(Question about this part....)

Question:

After entering the name in the combobox, and having the data entry form pop
up, the name has to be re-entered on the form. It would be nice if the data
that was already entered into the combobox could be entered automatically
into the appropriate fields on the data entry form. However, the combobox
rowsource is an expression which combines the fields to format the name to
display "AuthorLastname, AuthorFirstName AuthorMiddleName".

Would it be better to parse out the names from the combobox and push them
into the data entry form, or should I revise the form to use three
comboboxes (one for each field) instead of a single combobox (based on an
expression), and use the kind of drill-down process that Albert Kallal
advocates (and which I've never tried)?

Or is there a better solution? Or is it not worth the trouble to try to do
this?

Thanks!
Fred Boer

P.S. I've been on vacation, and haven't posted a question for a while, so I
thought I'd really write a long one this time... <g>



Table Structures:

Tbl_Library (Book title information)

Book_ID (autonumber)
Title
Format
Binding
Publisher
Place of Publication
etc...

Tbl_Author

Author_ID
AuthorFirstName
AuthorMiddleName
AuthorLastName

Tbl_BookAuthor

Book_ID
Author_ID

 

[Kelvin Lu]

I would suggest not using the combined names as the source for the combo
box. Use the AuthorID with the width set to 0. This way you can still show
the name, then just have the data entry form push the author id to match
what was just entered.

Kelvin

 

[Fred Boer]

Thanks for your response! Please excuse if I am being dense, but I don't
understand your suggestion... Sorry if my question wasn't clear; I am trying
to determine the best way to both search for an author (where three fields
are needed to uniquely identify the author), and how I can best take this
data from the search process and put it into a new record.

Don't know if that might help clarify, or not... <g>

Thanks!
Fred Boer

P.S. I would rather that you didn't have your newsgroup agent send any email
directly to me, if you don't mind... I will be sure to check back here for
any responses!

 

[Wayne Morgan]

Kelvin made a good suggestion. Do you have a unique ID field for each author in the table?
If so, this field should be included in the combo box's Row Source and be the bound field
for the combo box (and in your case the value you insert into the table using the
AfterUpdate event). Set the width property for this column to zero so that the ID isn't
visible to the user.

The combo box will normally do just as you are asking for a newly entered record when you
use the Response=acDataErrAdded line. The combined names may be giving you a problem. If
so, in the pop-up form, have the Save or Ok button save the new record then hide the form
(Me.Visible=No). This will return you to the NotInList event. You would use the
Response=acDataErrAdded line above to tell the combo box that there is new data and it
should requery itself. If this doesn't then list the new item in the combo box, place a
button next to the combo box. In the OnClick event of the button, set the value of the
combo box equal to the unique ID field of the new record from the pop-up form. You will
need a textbox on the pop-up form that is bound to this field in the table, set the
textbox's Visible property to No to hide it from the user if you wish. Since the pop-up
form is still open, just hidden, you can retrieve this value, then close the pop-up form.
If the new item is listed in the combo box, you should be able to close the pop-up form
instead of hiding it when you are done making the new record entry and, of course, the
button won't be needed.

For this to work (with or without the button), in the NotInList event you need to call the
pop-up form with the acDialog Window Mode argument in the DoCmd.OpenForm call, if you
aren't already.

 

[Fred Boer]

Responses inline:

Kelvin made a good suggestion.

Do you have a unique ID field for each author in the table?
Yes.

If so, this field should be included in the combo box's Row Source and be the bound field
for the combo box (and in your case the value you insert into the table using the
AfterUpdate event). Set the width property for this column to zero so that the ID isn't
visible to the user.

This is how I have it set up.

The combo box will normally do just as you are asking for a newly entered

record when you

For this to work (with or without the button), in the NotInList event you need to call the
pop-up form with the acDialog Window Mode argument in the DoCmd.OpenForm call, if you
aren't already.

Oh, ok... I've never used a form in Dialog mode... I think I see how this
could work:

Use the NotInList event. In case of vbYes, open the popup form in dialog
mode and add the data. On closing the popup form, acDataErrAdded will cause
the combobox to requery and try again. After reading your suggestions, I've
done a little reading on the acDataErrAdded response, and I see an example
in the ADH that appears to do what I want. Thanks so much for your help. I'm
off to give it a try - I'll post back if I have problems, but I think I'll
be able to do it! (Famous last words.... <g>)

Thanks Wayne and Kelvin!

Fred

P.S. Searching for "acDataErrAdded" in the "Help" returns nothing; searching
for "Response" returns nothing useful; I don't see anything under
"NotInList"... Could somebody please tell me what search term will bring up
information about "acDataErrAdded"??

 

[Fred Boer]

"Famous last words", indeed!

Ok, well, with the help of the ADH I have some of this working, but I still
want to do more...

Using the code reproduced below, I have managed to make the NotInList event
pop up the data entry form in dialogue mode. On exiting the data entry form,
the combobox is successfully requeried (yippee!). However, upon closing the
data entry form, I still have to go back into the combobox and select the
new data, so that the combobox's afterupdate event will cause the update to
happen. I want the update to happen automatically on closing the dataentry
form...

I've tried creating a variable, and then doing an insert, but I can't seem
to grab the correct, "newest" AuthorID....

I want to use these lines.....
__________________
intNewAuthorID = Me.Author_ID

sSQL = "INSERT INTO Tbl_BookAuthor (Book_ID, Author_ID) SELECT " &
Forms!Frm_LibraryDataEdit.txtBookID & " AS Expr1," & intNewAuthorID & " AS
Expr2"
Set db = CurrentDb()
db.Execute sSQL, dbFailOnError
_____________________

somewhere in the code below, .... but I although I've tried putting
"intNewAuthorID = Me.Author_ID" in various spots, it always seem to get the
first AuthorID in the table, not the latest....


Private Sub cboAuthor_NotInList(NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String
strMsg = NewData & " isn't an existing author. " & "Add a new author?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Author")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "Frm_EditAuthorList", _
DataMode:=acFormAdd, _
WindowMode:=acDialog
' Stop here and wait until the form
' goes away.
If ISLOADED("Frm_EditAuthorList") Then
Response = acDataErrAdded
DoCmd.Close acForm, "Frm_EditAuthorList"
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


Thanks for any non-painful suggestions! <g>

 

[Wayne Morgan]

I presume that the new information is showing in the combo box, but that the AfterUpdate
isn't firing because of the way the information got there, so you have to go back and
select it any way to get the AfterUpdate to fire. Is this correct?

One possible item would be to do the insert in the NotInList event using the data from the
pop-up form before you close it. Another possibility would be to call the AfterUpdate
event from the combo box's LostFocus or OnExit event.

 

[Fred Boer]

Hello!

I presume that the new information is showing in the combo box, but that the AfterUpdate
isn't firing because of the way the information got there, so you have to go back and
select it any way to get the AfterUpdate to fire. Is this correct?

I think that's it.... I know for certain that the new information is
correctly inserted into the table that is used as the rowsource for the
combobox, and that the combobox is being correctly requeried. However the
requery changes the combobox contents, which then requires the user to re-do
the combobox search...

One possible item would be to do the insert in the NotInList event using the data from the
pop-up form before you close it.

Yes, I tried that today, and, as I describe in the next post, I am having
trouble grabbing the correct Author_ID number; I tried to set a variable at
various places in the NotInList event, but it always takes the "first"
Author_ID in the table, not the "last" one..

I've also considered putting the Insert code behind the Close button on the
popup data entry form, but, as above, I haven't yet figured out how to grab
the correct Author_ID...


Thanks so much for your help!

Fred

 

[Wayne Morgan]

Since you say the record is in the RowSource table, the information should be available
from the pop-up form. Where the problem comes in, is that the combo box isn't actually
requeried until you exit the NotInList event (at least as far as I've been able to tell).
Normally, the combo box will still show the new data selected; however, since you are
using concatenated data, this may be causing a problem.

You should be able to access the ID field you need by referring to the textbox that is
bound to that field on the pop-up form. It's not needed to add the new data to the
RowSource table, but you need it for what you are trying to do afterwards. If you don't
have a textbox for this field, you may want to add a hidden one for this purpose. You
could then get that value by

Forms!PopupFormName!txtAuthor_ID

 

[Fred Boer]

Dear Wayne:

Thanks for sticking with me! I think I have it solved; well, at least I've
done something that seems to be working...

The popup form now includes an "Insert and close" button, which inserts the
newly added author information before closing the dialog form. If the user
doesn't want to go through with the insertion after added the record, they
use a simple Close button.

The "Insert and Close" button has the following code. Strangely, I have to
grab the AuthorID *before* I force the record to save. (This worries me a
little...). However, it seems to be working!

Private Sub cmdInsert_Click()
Dim db As Database, sSQL As String
Dim intNewAuthorID As Integer
intNewAuthorID = Me.Author_ID

If Me.Dirty = True Then Me.Dirty = False
sSQL = "INSERT INTO Tbl_BookAuthor (Book_ID, Author_ID) SELECT " &
Forms!Frm_LibraryDataEdit.txtBookID & " AS Expr1," & intNewAuthorID & " AS
Expr2"
Set db = CurrentDb()
db.Execute sSQL, dbFailOnError
Forms!Frm_LibraryDataEdit.ReturnToRecord = Null 'Code used in ensuring an
Author is entered for every title...

If db.RecordsAffected <> 1 Then
MsgBox "This is not a valid Book ID. Please try again.", _
vbOKOnly + vbInformation, "W. Ross Macdonald School"
Me.Undo
End If
Set db = Nothing
Forms!Frm_LibraryDataEdit!Frm_EditAuthorSubform.Form.Requery
Forms!Frm_LibraryDataEdit!Frm_EditAuthorSubform.Form!cboAuthor = Null
DoCmd.Close
End Sub

I really appreciate your help! Thanks again!

Fred

 

[Wayne Morgan]

I'm glad it's working. When saving the record through code, the form should stay at that
record. I just tried it with the form set as Data Entry only and it still stayed at that
record. However, grabbing the value before you save is working also. Another possibility
may be the "LastModified" property of the recordset.

 

[Fred Boer]

You were correct, Wayne... It was my own fault... I had some orphaned code
behind the form that was causing a requery and screwing things up...
(forehead smack!). Everything seems to work smoothly now..

Well, on to the next issue! Many thanks, again!

Fred