Linking lookup tables via forms 1

[Humlan]

None of my brand new Access 2003 books have any examples of the simple task I'd like to perform, so I'll put my faith in you guys...

Here's a screenshot with relations and all:

I have a subform displaying people connected to a case. These people are all linked via a lookup table. When displaying the case main form I get the subform by using this query:

SELECT tbl_Case_Type_Def.*
FROM tbl_Case_Type INNER JOIN tbl_Case_Type_Def ON tbl_Case_Type.Case_Type=tbl_Case_Type_Def.ID_Case_Type_Def
WHERE tbl_Case_Type.Case_ID=tbl_Case.ID_Case;

Problem 1:
Below the subform (in the main form) I've managed to add a combobox displaying all the people records. I'd like the button "Add to Case" to insert a row in the lookup table "tbl_Personal_Info" linking the current case with the selected person, if there isn't already such a record (avoiding a key error).

Problem 2:
I'd like the "Add Person" button to open the form "frm_Personal_Info_Def" with a new record, and if the record is saved (perhaps with a button in the form) then the same link between the case and the person should be formed. The subform should also be updated to show the newly added person.

This seems like a pretty trivial problem, but after spending 20 hours testing different solutions that hasn't worked, I'm beginning to lose my temper.

Thanks in advance!

/Jonas Hummelstrand

 

[Eupher]

Hi, Jonas:

I think you are right, what you are attempting should be pretty routine. Maybe a bit more info will help us point you in the right direction:

1) The WHERE clause in your subform SQL looks strange to me. Maybe I'm off base, but seems like there should be a join that includes tbl_Case if it's going to be included as a criteria.

2) Can you post the code behind your "Add to Case" and "Add Person" buttons?

3) Can you be more specific about the difficulty you are having? Error messages? Unexpected results?

4) To which table is your main form bound? How does it relate to tbl_Case_Type_Def and tbl_Case_Type?

Ken S.

 

[Dalain]

Hi, Humlan

I have used this type of setup many times. I call your table tbl_Personnel_Info a Linking table. This is the table that links the info between the other two. You can add info in the other two without causing any errors. But for the Linking Table You must have an existing record in the other tables in order to add a record.

First this what is the relation of tbl_Case_Type_Def to your other tables? (Not in you picture or did you change the name.)

Problem 1
On your Main Case Form using a Query joining the tbl_case and tbl_Personnel_info. You should also the a hidden field called Case_ID

I assume that the combo box displaying all personnel (I'll call cmbPerList)
Example: in the field properties
Row Source: SELECT DISTINCTROW [ID_Personnel_Info_Def], [First_Name]& [Last_Name] as FullName FROM [tbl_Personnel_Info_Def];
Bound to Column: 1
The field will actually save the ID number, but display the Full Name.

Now to add a personnl to your case File, on the On click event of the "Add to Case” button you would have

Private Sub AddToCase_Click()
Dim Test1 As String
'Check to see if the personnel is all ready there in this case
If DLookup("person", "tbl_Personnel_Info", "[Case_ID] = " & Me.Case_ID & " and [Person] = " & Me.cmbPerList) = "" Then 'Record does not exist
'Add the person to the Case
DoCmd.RunSQL ("INSERT INTO ,tbl_Personnel_Info ( Case_ID, Person) SELECT " & Me.Case_ID & "," & Me.cmbPerLis & ";&quot
'View Change in the Main Case form.
Me.Requery
End If
End Sub


Problem 2
This is a little easier. Your add Personnel Form should be based only on the table tbl_Personnel_Info_Def. In the Before Update Event you would have something like.

Private Sub BeforeUpdate()
Dim CurCaseID As String
'Get the current Case_ID Number
CurCaseID = [Forms]![YourCaseFormName].[Case_ID]

'Add the person to the Case
DoCmd.RunSQL ("INSERT INTO ,tbl_Personnel_Info ( Case_ID, Person) SELECT " & CurCaseID & "," & Me.ID_Personnel_Info_Def & ";&quot
'View Change in the Main Case form.
[Forms]![YourCaseFormName].Requery
End Sub

I hope this help, at the very lest it should point you in the right direction.

Dalain

 

[Dalain]

Oh, Humlan I almost forgot, how did you do that screenshot thing?

Dalain

 

[Humlan]

Thanks for your replies, I'll try to type quietly so that the family doesn't wake up (it's 2 AM here).

Eupher:
1. I was suprised how difficult it was to use a subform with lookup tables. A SQL-knowing friend helped me out with this one, and it works, but I just realized I had copied the wrong one. This is the one that is the base of the subform:

SELECT tbl_Personal_Info_Def.*
FROM tbl_Personal_Info INNER JOIN tbl_Personal_Info_Def ON tbl_Personal_Info.Person=tbl_Personal_Info_Def.ID_Personal_Info_Def
WHERE tbl_Personal_Info.Case_ID=tbl_Case.ID_Case;

I'm surprised that there are so few examples of subforms with lookup tables, none of the books I bought had any, and there is only one example in the Northwind DB.


2. Concerning the code behind the buttons, there isn't really much to show, as all the things I've tried haven't worked. I'd jump with joy if anyone could point me in the right direction with a link or an example of how to set them up.

3. I wish I could be more specific, but lacking any good examples I've messed around with DoCmd, but I'm unsure if this is the way to go. I've managed to bring with me the ID_Case to a new form by using

    strDocName = "frm_Find_Person_02"
    DoCmd.OpenForm strDocName, , , , acAdd, , Me!ID_Case

and in the opened form using

intID_Case = Forms!frm_Find_Person_02.OpenArgs

I just can't figure out how to:
A. Add an existing person (from the combobox) to the case, ie. create a new record in the lookup table tbl_Personal_Info if there isn't already an identical line linking the selected person with this case.

B. Let the user create a new person record in tbl_Personal_Info_Def and at the same time create a record in the lookup table tbl_Personal_Info, linking this newly created person with the current case.

4. Sorry for confusing you with the incorrect query on which the subform was based. The correct one is pasted above.

With great thanks in advance!
/Humlan

 

[Humlan]

The family hasn't woken up yet, so I'll continue...

Dalain:
tbl_Case_Type_Def was the wrong SQL, sorry for the mix up.

Thanks for your hand-on code, I'll try it out in the morning when I'm able to type a bit more freely.

Yes, the combo box does display all DISTINCT persons, but I had done it via a query which was a lot messier, so thanks for that clarification also!

Concerning the screenshot, I composited three screen shots in Photoshop. I'm more comfortable pushing pixels than tables.

Can anyone point me to good tutorials/books/sample DBs that have these kinds of things in them? I'm trying to adapt to VBA after using only VBS, but the syntax and methods of doing things are a bit tricker to grasp. It seems there are so many different ways of doing the same thing. Sample DBs are of course the best way to learn, poking around somethings that works and figuring out how it was done.

Thanks again, everyone!

/Humlan

 

[Dalain]

Hi, Humlan

Let me know how your code is working out.

To clarify (I should of been more specific) I was wondering how did you get the screenshot to display in the tek-tips forum.
I though you could only post text (and Smileys)

The one book that I have referred to in the past the Access Developer's Handbook. It is good if you want to know how the basics of VB works, but for more advanced things (like the linking tables you are tying to create) I had to develop on by own.

A good friend that I have been showing VB has often said to me that in MS Access books/help File he was unable to find examples of why or how the code that I was using worked. (And he had 3 or 4 books to look through)

As you have said "The best way to learn, poking around some things that works and figuring out how it was done" so here is a link so that you can download some Code, and do some of that poking around.

http://www.planet-source-code.com

Also don't forget the tek-tips forum. There are a lot of people here that are willing to help.

Dalain

 

[Humlan]

Thanks for all the help, I'll get started coding as soon as my wife wakes up, until then I'm confined to the couch watch morning cartoons with the kids.

Concerning the image, just add a link

[img]http://www.tek-tips.com/art/site/partner.gif[/img]

 

[Humlan]

Dalain,

Despite your example; I can't quite get it to work.

Problem 1:
I have added a hidden field containing the ID_Case, and called it [blue]edtCaseID[/blue].

The combo box I have called [blue]cmbExistingPersons[/blue] and the row source is

SELECT DISTINCTROW [ID_Personal_Info_Def], [First_Name] & [Last_Name] AS FullName FROM tbl_Personal_Info_Def;

Note that I've named the tables and everything Personal, not "Personnel."
The Control Source is empty and the Bound Column is [blue]1[/blue]

This combo box displays all distinct records in [blue]tbl_Personal_Info_Def[/blue] as their respective ID's only, which is not what you ment it to do, right? The FullName, how is that used?

The button [blue]Add Person To Case[/blue] has the following code. I had to switch it around a bit to get it to add records correctly, but it can't correctly detect if the record already exists, the If-clause always returns "True," perhaps because the combo box values doesn't quite work?

Private Sub btnAddPersonToCase_Click()
     'Check to see if the person is all ready there in this case
    If DLookup("person", "tbl_Personal_Info", "[Case_ID] = " & Me.edtCaseID & " and [Person] = " & Me.cmbExistingPersons) = "" Then 'Record does exist
        MsgBox ("Record exists")
    Else
        MsgBox ("Record doesn't exist")
       'Add the person to the Case
       DoCmd.RunSQL ("INSERT INTO tbl_Personal_Info (Case_ID, Person) SELECT " & Me.edtCaseID & "," & Me.cmbExistingPersons & ";")
       'View Change in the Main Case form.
       Me.Requery
    End If
End Sub

Also, the

Me.Requery

returns the main Case form to the first case record instead of to the current case. Any parameters I can sent to it, our how can I continue showing the current record?

************************

Problem 2:
The "Add New Person" button has the following code to make it open a new record:

Private Sub btnAddNewPersonAndLinkToCase02_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_Personal_Info_Def_Edit"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec
End Sub

The resulting form, with [blue]tbl_Personal_Info_Def[/blue] as Record Source has the following BeforeUpdate code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim CurCaseID As String
    'Get the current Case_ID Number
    CurCaseID = [Forms]![frm_Case]![Case_ID]
    'Add the person to the Case
    DoCmd.RunSQL ("INSERT INTO tbl_Personal_Info (Case_ID, Person) SELECT " & CurCaseID & "," & Me.ID_Personal_Info_Def & ";")
     'View Change in the Main Case form.
     [Forms]![frm_Case].Requery
End Sub

Running this, I get an error saying it can't find the field[blue]Case_ID[/blue]. If I switch the line to

CurCaseID = [Forms]![frm_Case].[Case_ID]

it complains it can't find the field "|".
If I hardcode the [blue]CurCaseID[/blue] I can add the new person record into [blue]tbl_Personal_Info_Def[/blue], but I get a key violation when creating the new record in the lookup table [blue]tbl_Personal_Info[/blue], even though I've created pairs that are unique. The hardcoded line looks like this:

    DoCmd.RunSQL ("INSERT INTO tbl_Personal_Info (Case_ID, Person) SELECT 6, 40;")

What am I missing?


Again, thanks for all help!
Sorry I posted in the wrong forum, I've just stumbled onto this excellent board...

/Humlan

 

[Dalain]

Hi, Humlan

Problem 1.

For your combo Box.
What you have is correct except a space between the first and last name would be nice.
SELECT DISTINCTROW [ID_Personal_Info_Def], [First_Name] & ' ' & [Last_Name] AS FullName FROM tbl_Personal_Info_Def;

In the properties also check the following.

Column Count = 2
Column Widths = 0";1"
Where 0 means the column will not be seen, 1 should be the same as the width of your field.)

This should fix your Combo Box

For your Add Person To Case

The problem is the Dlookup, if it returns an answer it means a record has been found. This one was my mistake it should have been <> instead of =.


Private Sub btnAddPersonToCase_Click()
'Check to see if the person is all ready there in this case
If DLookup(&quot;person&quot;, &quot;tbl_Personal_Info&quot;, &quot;[Case_ID] = &quot; & Me.edtCaseID & &quot; and [Person] = &quot; & Me.cmbExistingPersons) <> &quot;&quot; Then 'Record does exist
Etc... the rest is OK.

For the Requery.

From your picture, the Subject tab is displaying a table, which is a subform. When you add a person to the case is this where they are listed? If so then you only have to refresh the subform and NOT the main form.

Me![SubFormName].Requery


Problem 2

First the code [Case_ID] is wrong. The hidden ID_Case field that you created in the main form is what you are tying to reference. I believe you called [edtCaseID]

Also after adding a person, you still want to update the table info as you did with the add person to case.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CurCaseID As String
'Get the current Case_ID Number
CurCaseID = [Forms]![frm_Case]! [edtCaseID]
'Add the person to the Case
DoCmd.RunSQL (&quot;INSERT INTO tbl_Personal_Info (Case_ID, Person) SELECT &quot; & CurCaseID & &quot;,&quot; & Me.ID_Personal_Info_Def & &quot;;&quot
'View Change in the Main Case form.
Forms![frm_Case]![SubFormName].Requery
End Sub


An for the last part, the key violation.

Check that the Field types in your tbl_personal_Info are number type. If they are text then you will have to add &quot; in the code.

DoCmd.RunSQL (&quot;INSERT INTO tbl_Personal_Info (Case_ID, Person) SELECT &quot;&quot;&quot; & CurCaseID & &quot;&quot;&quot;,&quot;&quot;&quot; & Me.ID_Personal_Info_Def & &quot;&quot;&quot;;&quot

Also for the key violation, you could always open the tbl_Personal_Info_Def and add the record manualy. This may give you the answer to why there a problem. If you have a problem here then the code is not the problem. Double check the table field settings and relationships.

Well that all for now. let me know how it goes.

Dalain

 

[Humlan]

Dalain!

You've saved my day, a thousand thanks!!!

It works great, and I just realized that the key violation was caused by using the BeforeUpdate event; the record I tried to insert contained a Person key that hadn't yet been created! Using AfterUpdate, everything works.

Now I just have a sleepless night ahead of me, trying to finish as many features as possible before visiting the client tommorrow morning at 9 AM. :-/

I've ordered the upgrade to Office 2003, but now I don't have to bet on that MS has turned the lookup tables in subforms into a wizard!

Again, many thanks, you're the man!

/Humlan