Join Key not in Record Set!!!

[Megacitizen]

I keep getting this legend in the bottom left of my form when I try to key in data into some of my fields.

The main relationships between all relevant tables is fine ie PKs and FKs correctly set up, but I am unable to see where any other joins are necessary. The form is being designed to only display the fields which are text type data, all IDfields will be zero length strings.

The tables are as follows:-

t_Bases
BaseID
Base
NationID

t_Nations
NationID
Nation

t_Teams
TeamID
BaseID
TeamName
Type
RegionID

t_Regions
RegionID
RegionName

The fields in bold are those which we intend to display on our form. The Nation and RegionName fields are the only ones where I can currently key in any data. Our intention is to use the form to populate all the relevant fields within our tables.

Any advice?

 

[Megacitizen]

Teams is a junction table that relates bases to regions. the form should be based on a query of Teams. RegionName and Base should be combos. You don't need to include those tables in the RecordSource query.

Please post the query, if my suggestion doesn't solve the problem.

Quick query - I have the two fields you name set as Text boxes only. To convert to combos, should I be making them lookup fields in the Table Design? Or should I be deleting them and replacing them with Combo boxes?

Sorry for the basic stupid questions, I honestly have no idea what I'm doing here . Sometimes, the On-Off switch on my laptop seems like the limits of my technical know-how .

 

[Megacitizen]

Lookups should be defined on forms NOT tables. Delete the two text boxes and add them back as combos. The wizard will help you build them.

I think my Wizard's gone on strike, it's nor firing up when I add my combo boxes to my form. Not sure whether that's a system fault or Wizard's been turned off on my system (shouldn't be as it works on other elements of Access).

 

[Megacitizen]

My superiors have now decided they don't want combo boxes anywhere at all. Instead, ALL data is to be keyed into text boxes (or other defined data types where applicable) as new info, from the first teams and Bases onwards.

We have about 120-130 bases currently registered on our own DB as having shown an interest in our project. (Some have been and gone already, others currently in our Target city and more in the pipeline to go in July/August.) But we have up to another 1800+ Bases (in nearly 200 nations - we have nearly 50 represented so far) to theoretically add if (and only if) any of them declare an interest in our Project. We will also have an indeterminate number of teams and/or individuals from other organisations joining in. We need to be able to add those in via our forms as well. Basically, it's a glorified Data Entry form which will also eventually display results of Teams' actions on the same page.

We had an older DB (A2003(?)) which was set up without using combo boxes anywhere and the powers-that-be want the new DB to be the same as the old one, presentation-wise at least - the engine underneath may be newer and more efficient but it's the same surface on display. The differences will be the actual data stored and what queries we run from such.

I have managed to set the form's data entry mode to Yes but am not sure about the other codes I need to key in and where.

 

[Megacitizen]

If combo boxes have to be used (something my seniors are extremely reluctant with), then hopefully, there is a code to write somewhere, so that as the user types the info into the field, the drop down box would automatically reduce in length until there are few enough choices for him/her to be able to make the correct option, hopefully by the third letter. We won't want them scrolling all the way through a list of 184 nations, when typing in the first letter will automatically eliminate all but about a dozen straight away (21 nations on our list begin with 'S'). Same with the Regions field. Also, if the user types in an incorrect spelling eg they try to type "Ginea" instead of "Guinea", as there are no nations starting with the letters "Gi..", we would want a macro at that field saying "Invalid Entry" or similar.

One of my biggest concerns is that some of our potential users do not have English as their first language, whilst others do suffer from Dyslexia. I've seen some strange spellings for one or two Countries and cities in the past.

 

[Megacitizen]

Latest updates, combo boxes are to be allowed where necessary, for the reasons stated on here. We just need to work on getting the Team Name field populated by keying the data into it. That is the key field that will make each page in the DB unique.

I have been given a "newer" DB program to work from - actually a downgrade to A2007 on the Base Server (the one that crashed a few weeks ago wiping all work I'd done up to that point) from A2010 on my own laptop. I can't attach a copy here as it's way too big, even after stripping out redundant files and then compressing and zipping. But I'm still suffering the same problem which led to this thread in the first place.

I've managed to work out SOME of the codes needed, but not sure of the ways to start them or where in the property lists they go. Any help will be gratefully appreciated.

 

[Megacitizen]

I found, from another forum, the following code for adding new data to the School or Ministry field in my form. However, the writer of the thread says it should go in the NotInList event procedure - something I cannot find.

Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to schools list?"
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmSchools", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmSchools closed
DoCmd.Close acForm, "frmSchools"
' ensure School has been added
If Not IsNull(DLookup("SchoolID", "Schools", "School = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Schools table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
I put this in the AfterUpdate event of my form field, but now think it should have gone in the BefureUpdate event. Is that correct?

Also, the above string suggests using a seperate form to enter the new details into our tables, whereas we are intending using the original form for ALL data entry. How should I recode where necessary?

 

[RainLover]

Re The Wizard not working when you add a new Combo from the Toolbox Menu.

Have a look at the far left Icon and click on it. This turns the Wizard on or off. It stays in that state until you click on it again.

My instructions are for 2003 or earlier. Not sure about all this Modern Stuff. 2007 and 2010. I leave them you the young folk like you and Pat.

 

[Megacitizen]

The NotInList event occurs only for combos.

As a matter of safety, I don't allow users to add new items to the list presented by the combo. In my mind that defeats the purpose of having the combo at all since the users will simply insert their own spelling errors rather than look further than their nose for an existing entry. I would recommend creating a table manitenance forms that only admins can use to add new entries for various lists. If you limit the number of people who can add entries to lists, you limit the possibility that someone will carelessly add a spelling error duplicate.

Good idea about the Maintenance Table/Form, although for our Nations table, it is unlikely to be required for sometime, at least until we get a Base or two established in any of the 40-odd Nations not currently on our list. In fact the only maintenance I can currently envisage is to REMOVE a couple of listings. I lifted the info from an old Citizenship list and removed all the Dual Citizenship rows, but missed 1. I also have two listings for Congo - Republic of.. and Democratic Republic of.. (why they couldn't simply remain being called Zaire.....)

 

[Megacitizen]

My instructions are for 2003 or earlier. Not sure about all this Modern Stuff. 2007 and 2010. I leave them you the young folk like you and Pat.

So nice to be called young again On my base, the vast majority of staff and students are young enough to be my own kids (if I had any). Although to count those older than me, I'd still need the fingers on both hands and those of a colleague or two - much safer than taking my shoes and socks off

 

[Megacitizen]

OK, after much googling, trolling of this and other websites/forums and reading up in various books, I think I've come up with the right codes to establish Joins. Unfortunately, the books, especially, don't necessarily tell me WHERE a lot of the codes go.

select school or ministry name, Base Name, Country
from schols, Bases, country
where school or ministry name.schoolID = school or ministry name.BaseID

Not sure if this should go in the Form's event property or the field's event property or in another property. Also, does it need any extra lines and/or info. Scenario is:- a team will be from a base which in turn will be from a particular country or nation.

I could set up an auto-populate of the Base and Country fields once the school name is keyed in, but it's getting that info keyed in, in the first place, that's my biggest issue - auto-populating is not a priority at this time.

I can't upload a copy as the file is way too big, even after zipping - it's over 3MBs zipped.

 

[RainLover]

Two Things.

First of all you could post the DB if you made a copy and deleted all the stuff we don't need to see.

Second. You could build the Query in the query grid. Once working, if you wanted to you could convert it to SQL.

Perhaps you should learn the second method so that in future you will be able to solve similar problems.

 

[Megacitizen]

Have got an empty DB here, no entries made in any fields. This could actually be the cause of some of my problems, I am trying to set my form up as a Data Entry form so the user will key in the information as they have it. The form is intended to be a progressive report and therefore be updatable over a period of time until all relevant stages of the Teams' progress have been completed and recorded.

Got rid of a table, which I realised could be irrelevant (duplicate info) and merged some of the required fields from it into an existing table.

I'm pretty certain all my relationships are properly normalized. I just need to know what SQLs go where so that I can get my joins correct in the RecordSet.

View attachment Megacities Stats.mdb

 

[RainLover]

Why do you want to join SchoolID to BaseID.

Should it not be BaseID to BaseID.

Go to Query Design and add the three tables "School or Misintry" , Bases and Country.

This will give you a Graphic view that should help.

This would be your SQL

SELECT [school or ministry].*, Bases.*, country.*
FROM country INNER JOIN (Bases INNER JOIN [school or ministry] ON Bases.BaseID = [school or ministry].BaseID) ON country.NationID = Bases.NationID;

This could go in the Record Source of the Form, or save it as a Query and use the Name of the Query as the Record Source.

PS

Your design could be Normalised a bit better and you could also use better naming conventions.

 

[Megacitizen]

Thanks Rain, works much better now.

Have changed a few table and field names to comply better with Naming conventions, as you suggested, and have slightly revamped some of the Normalizations. However, the Base and School relationships ARE correct. Because they are one above the other in the TeamMember table, it does look as though I've got them mixed up, but on closer inspection, I haven't.

 

[RainLover]

That's very nice of you to say.

So I won't mention the Lions or Eagles.

 

[Megacitizen]

S'ok Lions deserved it, probably more because the Eagles were so bad in the first half, they didn't seem to want the ball.

Don't know why but WA teams don't seem to have a good record against QLD sides in any sport, although the WA Raiders did beat QLD Sundevils in the Gridiron Australia National Championships this year, on their way to the title .

 

[Megacitizen]

After having to redo the form, to try and solve a number of seperate issues, I find I now cannot get this query to work. I've tried deleting and rewriting it, but to no avail.

Do I need a seperate code to re-query the issue?

 

[RainLover]

Can you open your query in Datasheet View.

Does it show the Data you expect to see.

Can you Add Delete Edit.

To the Form.

Does the Form have the query as its record source.

Does the form display the same data as the query.

 

[Megacitizen]

Can you open your query in Datasheet View. Yes

Does it show the Data you expect to see. Only if I expect to see all fields in the three tables concerned. I only really need to see the three fields I require.

Can you Add Delete Edit. In Datasheet or Table use - Yes

To the Form.

Does the Form have the query as its record source. It does

Does the form display the same data as the query.

The form is currently blank. I can enter stuff in the Bases and Nations combo boxes but not the School_Ministry field. We want the form and relevant subforms to be able to input ALL details.