Any idea how this is done???

[pfenton]

I'm building a db under someone else's instructions! Some of the things they suggest are a bit new to me.
Basically there's 4 tables and some links between them (I'm not sure if he means linked by relationships!?)
For 1 table (tblJunction) there are three fields and this is what I've been instructed to do - please help (not part of the instructions!!!)
FieldName
Junction ID - Autonumber-Primary (not a problem)
UserID - number - "This will be a drop-down box linking to the tblUser, in this box you will have set
2 Columns
1st column being bound
Set Limit to List to Yes
The first column will be
UserID
The second column will be
User: [tblUser]![Firstname]&""& [tblUser]![Surname]
Column Widths: 0cm;3cm
Sort: Ascending
Criteria: Is Not Null"

There is a third field in this table with similar instructions which I won't write (no reason, just lazy!)
Can anyone tell me where these links are, em linked and also how do I set properties for the second column (Query?)
And does he mean Combo boxes when he says drop-down (I thought so because list -boxes don't have the 'Limit to List' property????

Be gentle!!!

 

[kathryn]

The junction table you are building is a very common construct in Access. It is used to link two tables that are in a many-to-many relationship. Your two tables, tblUser and tblWhatever must meet the following criteria:

Each User can have (or can be associated with) many Whatevers. Every Whatever can have many Users.

What you are building is the form that will build the relationship between these two entities, Users and Whatevers.

When the instructions say drop-down, it means combo boxes.

In your form, when you create the combo boxes, make sure that the wizard is turned on. (the wand button must be depressed)

I would create a query with one field of UserID and the second field a calculated field: User: [tblUser]![Firstname]&""& [tblUser]![Surname]

Then when you create the combo box, base it on this query. The wizard will step you through most everything. Make sure that you hide the UserID column; this is an option in the wizard.

To set the LimitToList, you will have to go into the properties of the combo box after you have finished.

I hope this helps you get started. If I misunderstood your problem, or if I can be of more help, let me know.


Kathryn

 

[pfenton]

Kathryn, thanks very much!
Are you saying I've got to build some kind of form to link Users and DocumentRevisions ("Whatevers&quot??????
Another Q - A field in the table tlbDocumentRevision is called Revision and I'm asked to set the Data type as "Double(Create Input Mask to reflect Revision Entry)" - whatever this may mean???
Any help would again be appreciated, from anyone??

 

[kathryn]

Actually, re-reading your original post, it looks like your instructor wants you to build the lookups into the table design. The process is similar to what I said before. Create the query with User ID and the new User field made up of the first and last names.

go into table design of tblJunction and after entering the name of the field, UserID, choose Lookup Wizard (I think it is the last choice, for the data type. The wizard is almost the same as the combobox wizard, because they function similarly.

No idea what is meant by the Input Mask for revision, sorry.

Kathryn

 

[pfenton]

Kathryn,

I think so too, about building the lookups into the table design.
Have I got to create the query first and then Append it to the table ?

Sorry to keep bugging you!!!

 

[kathryn]

No, you want to create the query, call it qryUserName. Then when you use the Lookup Wizard, it should let you pick from Tables or Queries to base your look up on.

does that make sense?

Kathryn

 

[pfenton]

ok ok - lets see!!
I'm creating a query with 3 fields - User ID, Surname and Firstname!
I'm taking these fields from the tblUser!
Please say yes??!!
Thanks

 

[kathryn]

Close, buy no cigar.

You are creating a query, which USES three fields, but only has two fields in the result.

The first field is UserID. The second field is being created/calculated 'on the fly'. This is the User field. To create a field in a query, type the field name, followed by a colon, followed by the calculation. So in your case, in the second column of the query, you would type:
User: [tblUser]![Firstname] & " " & [tblUser]![Surname]


When you run the query, the results should show two columns, and the second column should be a concatenation of first and last names.


Kathryn

 

[pfenton]

Well fair play to you..
At long last I'm getting somewhere (and the database is coming along nicely too!!! - joke!)
OK, now it's not!!
When I'm going through the lookup wizard, it's telling me that "no value given for one or more required parameters"
Any ideas of this problem - 'cos I've got the query done right, as you said!?

 

[kathryn]

OK, believe it or not, I just created a tblUsers and a query based on it. I then created a new table, tblJunction, with two fields: JunctionID (AutonumberPK) and UserID, datatype Lookup Wizard. I said that I wanted to look up values in a table or query, then on the next page, choose Queries, and choose the query I had created. Then I selected both fields, UserID and User. Next page asked about the width, which was fine. The next page asked which value I wanted to store. I chose UserID. The next page asked what I wanted to name the field and I said UserID and clicked Finish. I tested the field and it works.

Could you tell me where you are getting the error message?

Kathryn

 

[pfenton]

I'm getting the error message when I'm selecting the fields to contain the value i want included in the lookup column!
But this is with a different table, the one including the Input Mask to Double and the whole Revision thing I mentioned earlier!!!
Because I don't know how to change the input Mask to 'Double' I think this is where the trouble lies???

 

[kathryn]

Whoa. You just totally lost me. I think you may be trying to do to much at once. Have you gotten the lookup for UserID working?? The lookup for UserID only includes the two fields from the query, so I am not sure what the "different table" is.

I just noticed in your original post, that there was a criteria of Is Not Null in the User field. You may want to add that criteria to the query you created with UserID and User.

Kathryn

 

[pfenton]

Sorry, the lookup for the UserID is now NOT working again!!
I also had to create 2 other query's as lookups (as part of the d/b, not for this table but they're going fine!)
For this line of code you provided this is what results in its place when the query is reopened!
Yours: User: [tblUser]![Firstname] & " " & [tblUser]![Surname]
Mine: User: [tblUser].tblUser![Firstname] & " " & [tblUser]![Surname]

So the question now is - your place or mine (sorry, couldn't resist!)
This is not good + it's nearly home time (I'm in Irreland!)

 

[kathryn]

I think mine is correct. Have a good night. We can continue tomorrow. The good news is that I come to work at 7 am (I am in Florida). The bad news is that now I want at LEAST a pint of Harp's for all this work.

Kathryn

 

[pfenton]

I got that User one sorted (fixed!) but I've had to create another one which, as I mentioned earlier involves the Input Mask set to Double etc....
So tomorrow's another day!

Anyway, Kathryn cheers for all the help. I'll buy you the pint of Harp (prefer Guinness myself!) next time your over here!!
Cheerio