By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,883 Members | 2,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,883 IT Pros & Developers. It's quick & easy.

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

theaybaras
P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. [Author_Info]![Author_Last] & 
  2. (IIf([Author_Info]![Author_First] Is Null,'',', ' & (Left([Author_Info]!
  3. [Author_First],1)))) & (IIf([Author_Info]![Author_Middle] Is Null,'','. ' & 
  4. (Left([Author_Info]![Author_Middle],1)))) & (IIf([Author_Info]![author_middle2] Is 
  5. Null,'','. ' & (Left([Author_Info]![author_middle2],1)))) & (IIf([Author_Info]!
  6. [author_middle3] Is Null,'','. ' & (Left([Author_Info]![author_middle3],1)))) 
  7. & '. '
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!authorCity). 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
May 8 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
Hopefully...this 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.
Expand|Select|Wrap|Line Numbers
  1. Function RefreshCombos(strForm As String, strSubform As String)
  2. Dim f As Form
  3. Dim c As control
  4. Dim strVal As String
  5. Dim strField As String
  6. Set f = Forms(strForm)(strSubform).Form
  7. For Each c In f
  8.     If TypeName(c) = "combobox" Then
  9.         c.SetFocus
  10.         c.Requery
  11.     End If
  12. Next c
  13. End Function
  14.  
Now in the on_close event of your Author form..

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. RefreshCombos "myform"
  3. End Sub
  4.  
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
May 8 '07 #2

JConsulting
Expert 100+
P: 603
Hopefully...this 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.
Expand|Select|Wrap|Line Numbers
  1. Function RefreshCombos(strForm As String, strSubform As String)
  2. Dim f As Form
  3. Dim c As control
  4. Dim strVal As String
  5. Dim strField As String
  6. Set f = Forms(strForm)(strSubform).Form
  7. For Each c In f
  8.     If TypeName(c) = "combobox" Then
  9.         c.SetFocus
  10.         c.Requery
  11.     End If
  12. Next c
  13. End Function
  14.  
Now in the on_close event of your Author form..

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. RefreshCombos "myform"
  3. End Sub
  4.  
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....

Expand|Select|Wrap|Line Numbers
  1. Function RefreshCombos(strForm As String, Optional strSubform As String)
  2. Dim f As Form
  3. Dim c As control
  4. Dim strVal As String
  5. Dim strField As String
  6. If Nz(strSubform, "") = "" Then
  7. Set f = Forms(strForm)
  8. Else
  9. Set f = Forms(strForm)(strSubform).Form
  10. End If
  11. For Each c In f
  12.     If c.controlType = acComboBox Then
  13.         c.SetFocus
  14.         c.Requery
  15.     End If
  16. Next c
  17. End Function
  18.  
May 8 '07 #3

theaybaras
P: 52
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?
May 9 '07 #4

JConsulting
Expert 100+
P: 603
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...reason 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
May 9 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.