requery and comparison help? (i've read the requery/refresh discussions)

[theaybaras]

Hi All...
I've read both of the requery discussions I could find...

Refresh ComboBox After Adding New Values via a Seperate Form

and

refresh a form

but am not quite able to get this to apply to my database.

I have a table/form called Author_Info where information about authors is entered. The fields of interest here are:

Author_Last
Author_First
Author_Middle
author_middle2
author_middle3

I have a query called Author_Concat that takes the information
from these 5 fields in a specific way (in a field called Expr1)

for instance, if Author_Last is Rothbaum, Author_First is Barbara,
Author_Middle is Olasov, and author_middle2 and author_middle3 are
blank... the query takes the last name, adds a comma then takes the
first letter of first name and then a period a space and the first
letter of the middle name then a period. Rothbaum, B. O. (no space after R.)

if she had 2 more middle initials/names (say, Barbara Olasov J. Rayme
Rothbaum; and that information was entered properly in the fields)
the query would return Rothbaum, B. O. J. R. (no space after R.)

the expression I have in my query is:

[Author_Info]![Author_Last] & 
(IIf([Author_Info]![Author_First] Is Null,'',', ' & (Left([Author_Info]!
[Author_First],1)))) & (IIf([Author_Info]![Author_Middle] Is Null,'','. ' & 
(Left([Author_Info]![Author_Middle],1)))) & (IIf([Author_Info]![author_middle2] Is 
Null,'','. ' & (Left([Author_Info]![author_middle2],1)))) & (IIf([Author_Info]!
[author_middle3] Is Null,'','. ' & (Left([Author_Info]![author_middle3],1)))) 
& '. '

I then have a form/table called Articles with 12 comboboxes: a_author1, a_author2, a_author3 ... a_author12, a_author12. each one of these looks up Expr1 from the query Author_Concat. The information from each field match the following (# refers to the # 1-12 of the author field)

Control Source: a_author#
Row Source Type: Table/Query
Row Source: SELECT [Author_Concat].[Expr1] FROM [Author_Concat]
Limit to list: Yes
Auto Expand: Yes

all other data options are blank

all events are blank

The user will enter information (more than the name... including contact information, affilition(s), specialty, etc.) before being able to enter information about the article (year, title, journal, volume, issue, pages, type, data-collection method, etc.)

My question is two fold.

First, if the user is entering articles, and needs to add an
author, and they open the Author_Info form (I don't have a button for this except in my switchboard, i'm not sure how to use the docmd thing. ) and enter in the information.
How do I get all 12 comboboxes to requery without jumping back to the first record, or reopening the form. i have tried implementing the various code suggestions mentioned in the discussions linked above to no avail.

Second, how can I make sure that an author is not entered into Author_Info twice? There may be 2 people with the last name Creamer (and maybe even the first name Mark)... but these are separate fields. Is there a way I can maybe ... concatenate the field of that record (in an unbound field maybe?) and compare this to my query.
if the information from my fields in a record concatenate to Creamer, M. A. and there is already a Creamer, M. A. in the query Author_Concat bring up a dialogue box that says something like

"A record for "Creamer, M. A." already exists in this table, and is from (refer to Author_Concat!a uthorCity). If this is a duplicate entry please click "Cancel" if this is not a duplicate entry please click "OK"

This question isn't as important as we can live with duplicate entries until they can be doublechecked and removed. But is something I'd like to look at.

I'm terribly sorry for the long question, but I wanted to provide as much background as I could. Anyone have a suggestion? My coding ability is almost none... although I got the mess of IIf statements to work without issues... ;)

Anyway, I'd appreciate any guidance you can provide!

Thanks!

theAybaras

[JConsulting]

Hopefully...thi s will help you out.

Your flow (since you aren't launching the form from your form...will mean that you first have to click on your switchboard, then open up your author form...you add data and save and close.

when your form closes...focus is returned back to your switchboard where you once again have to click back to your other form.

Now that that's out of the way...

Since you're updating authors and saving and closing...you need an On_Close event for that form.

your event will have to go out and set focus back to your other form (not the switchboard) and execute a loop to go through your 8? combo boxes to refresh them (if I read your Q right). So..

A quick function that will cycle through the combos on your form and requery them. This will go into a CODE module.

Function RefreshCombos(strForm As String, strSubform As String)
Dim f As Form
Dim c As control
Dim strVal As String
Dim strField As String
Set f = Forms(strForm)(strSubform).Form
For Each c In f
    If TypeName(c) = "combobox" Then
        c.SetFocus
        c.Requery
    End If
Next c
End Function
 

Now in the on_close event of your Author form..

Private Sub Form_Close()
RefreshCombos "myform"
End Sub
 

that should add the changes you just made to the combo boxes. so when you get back to the form check to be sure.
J

[JConsulting]

Hopefully...thi s will help you out.

Your flow (since you aren't launching the form from your form...will mean that you first have to click on your switchboard, then open up your author form...you add data and save and close.

when your form closes...focus is returned back to your switchboard where you once again have to click back to your other form.

Now that that's out of the way...

Since you're updating authors and saving and closing...you need an On_Close event for that form.

your event will have to go out and set focus back to your other form (not the switchboard) and execute a loop to go through your 8? combo boxes to refresh them (if I read your Q right). So..

A quick function that will cycle through the combos on your form and requery them. This will go into a CODE module.

Function RefreshCombos(strForm As String, strSubform As String)
Dim f As Form
Dim c As control
Dim strVal As String
Dim strField As String
Set f = Forms(strForm)(strSubform).Form
For Each c In f
    If TypeName(c) = "combobox" Then
        c.SetFocus
        c.Requery
    End If
Next c
End Function
 

Now in the on_close event of your Author form..

Private Sub Form_Close()
RefreshCombos "myform"
End Sub
 

that should add the changes you just made to the combo boxes. so when you get back to the form check to be sure.
J

Slight Mod to the function here....

Function RefreshCombos(strForm As String, Optional strSubform As String)
Dim f As Form
Dim c As control
Dim strVal As String
Dim strField As String
If Nz(strSubform, "") = "" Then
Set f = Forms(strForm)
Else
Set f = Forms(strForm)(strSubform).Form
End If
For Each c In f
    If c.controlType = acComboBox Then
        c.SetFocus
        c.Requery
    End If
Next c
End Function
 

[theaybaras]

hi!~ thanks so much for your help! I'm trying to get this to work. I have figured out how to add a button to open the Author_Info form.

How might this change in flow affect how I will requery the the 12 comboboxes since focus will go to the Articles form on close rather than switchboard?

[JConsulting]

hi!~ thanks so much for your help! I'm trying to get this to work. I have figured out how to add a button to open the Author_Info form.

How might this change in flow affect how I will requery the the 12 comboboxes since focus will go to the Articles form on close rather than switchboard?

it shouldn't...rea son is you're passing the name of the form to the function. So as long as that form is open, you should be good to go.
J