I have a form that contains a combo box for the user to select an address from. What I would like to do is create a pop up form that will open as soon as the user selects the address and if there are any prior records for that particular address then the pop up will show the information. I am sure this is possible but I really don't know quite how to accomplish this. Please keep in mind that I do not have any VB training so therefore most of my work is with macros and such. Any help is greatly appreciated. Thank you,
There is a wizard for doing what you ask. The combobox wizard has as its third option "Find a record based on the value I select". If you select that and follow the rest of the wizard you will get an UNbound combo box where you can choose an address and go to the record it is linked to. I'd put this in your Form Header, perhaps.
If you look at the code behind it you might be able to see how it does what it does. I honestly don't know the Macros way to accomplish this, if it is even possible, but with a little more information I can probably help you with the code you'll need to do it.
Another alternative is if you want to pull up only those records that match this exact address. That would be a Filter or Where statement, and can be done with a Macro. However it would have to match exactly unless you specify otherwise...
However how is the address currently associated with the complaints? It looks like it is all in the same table currently, which would make it...problematic.
Here's an alternative idea, if you have the ability to change your system:
Main table: Addresses.
Store the Location of Complaint, Legal Description, Additional Information (maybe), Owner Information, etc in this record. This will serve as your main record for that address. Make sure the table has a Primary Key - probably Autonumber.
Now make an additional table for Complaints.
This table holds the SR-Number, Date of Complaint, Referred Date, Officer taking Complaint, Reporting Party Information, Nature of Complaint, and Action Taken (possibly - if there can be a series of actions, they belong in their own table, linked one-to-many to this one), etc. Are the SR numbers unique to each request? If so that field can be the Primary Key of this table. One more field is needed to tell Access that the two are linked: a field of compatible type to the PK of your Addresses table. If it's Autonumber, make this field Integer. If it's Text, this one can be Text.
Go into Tools>Relationship and draw a line between the two matching fields on your two tables. It should say One-to-Many in the lower left. Go ahead and enforce Referential Integrity, and Cascade Update, but perhaps not Cascade Delete (I'll explain why in a moment).
Now you're going to have to fiddle with your forms a bit, but I think you'll like the end result better.
Your main form will hold the information about the Address itself. in the Header you can put a combobox of the type I listed above, "Find a record based on the value I select".
Now build a subform with all of the Complaint fields on it. There is a tool in the Toolbox for Insert Subform/subdatasheet. Use it and it will help you link your two forms correctly when you insert it. You only want to show those records where AddressID (or whatever you're calling that field) matches.
Now when you call up an address by scrolling to it or selecting it from that combo box, the subform will show all existing complaints on that address. Here's an example of code that will go to the existing record, if it exists, or go to a new record if not.
Code:
Private Sub AddressLookup_AfterUpdate()
' Find the record that matches the typed value.
Dim rs As Object
If (IsNull(Me.AddressLookup)) Or (Me.AddressLookup = "") Then Exit Sub
Set rs = Me.Recordset.Clone
rs.FindFirst "[Address] = '" & Me.AddressLookup & "'"
If rs.Nomatch = True Then 'no match found - go to new record
If (Me.NewRecord = True) Then 'already in a new record
If (Me.Dirty = True) Then Me.Undo 'clear any junk out of it
Else
DoCmd.GoToRecord , , acNewRec
End If
Me.Address = Me.AddressLookup 'fill in the field so far.
Me.AddressLookup = "" 'empty the lookup field
Else
'Go to the matching record
Me.Bookmark = rs.Bookmark
End If
Me.AddressLookup.Requery
Set rs = Nothing
End Sub
One consideration in city complaint management is whether the complaints are tied to the Homeowner or the Address. If they're tied to the Homeowner, then when the property changes hands you can make a new record for the same address with the new contact information. If this is so in Salina, I'd add a column to the combobox above that shows the Homeowner, so they can pick the right record.
In that case I'd put a checkbox on the main form for "Obsolete" and only show those records from the table where [Obsolete] = False.
Cascade Delete will delete the subrecords if you were to move those Obsolete records to another (possibly Archived) table. That's why I said you may not want to check it.
Hopefully that helps. Post back if I've thoroughly confused you.
First of all I want to thank you for all of your assistance. I know that you are very busy considering the work you do.
I actually have my tables already set up the way you suggested doing them. It's nice to know that I have done that correctly. I think sometimes deciding the layout and design of the DB is more difficult than putting it all together. Anyway, I have attached another screen capture of the new form I designed with your suggestion of the subform. I really like that idea and have included some notes on the only problem I am having.
So is "Location of Complaint" the lookup combo box, or the stored field? You can use the same field for both, but I personally find it exorbitantly difficult...
I'm a little confused by your structure still. It looks like you've got "Nature of Complaint/Request" on the mainform still, and on the subform. Unless you've got a fancy 'enter it on the main form and it gets saved to the subform', you may want to look at that....
As for the subform not refreshing, try putting this at the end of your AfterUpdate code (I took it out of my example, forgetting you'd have one when you redesigned):
Me.subformName.Requery
That should show the subform records correctly when you jump to a new record.
The City of KCMO stores Number, Direction, Street, Type, and Apartment all in separate fields for the GIS database. It may be atomic, but it's annoying as hell to use.
For most of my purposes I find the first line of Address to be sufficient, and I can separate it out when I need to. Carolyn's needs may be different out west.
