Control Source for Unbound Text Box (1 Viewer)

[Robecca]

I am designing a bowling database for our summer league (my brother's brainstorm).

I have a table Score that has the following fields:
ScoreID (AutoNumber)
ChkInID (FK - Number)
GameID (FK - Number)
LaneID (FK - Text)
Score (Number)

So, for each bowler and game, there will be 1 entry into this table.

I built 4 tables (Game1 - Game4) to capture the 4 games we bowl each week. However, on Game2, my brother would like to reference for each bowler their previous game. So by the time we get to Game4, we see games 1-3 and then enter Game4 and have a text box for series.

I haven't even thought about series. I'm stuck on getting the unbound text box on Game2 to reflect the game bowled from Game1.

I thought I would just go to the Control Source for that unbound text box and expression builder and choose Game1 from my previous form. But that returns #Name?. The same thing if I try the query. I tried dlookup but all I got was the first bowler's Game1 for every one.

Any ideas?

 

[Isskint]

Dlookup() is the answer. You just need to restrict the results to that bowlers name. Something like Dlookup("Score","LastGame","Bowler='" & BowlersName & "'")

 

[Robecca]

I am getting the syntax wrong. Here's my dlookup

=dlookup(field,table,where clause)
=dlookup([Score],"Score",where Score.ChkInID = Form.Game2.ChkInID and Score.GameID = 1)

=dlookup([Score],"Score","ChkInID = ' " & Forms![Game2].[ChkInID] & " ' and [GameID] = ' " 1)

I've googled and none of the multiple criteria examples work for me. I'm pretty sure I have to used ChkInID, but I'm not sure how to reference the table of Score vs the form Game2.

 

[Isskint]

Very close. Each of the arguments needs to be a string so [Score] needs to be "[Score]". Also in the where you were including a space before and after Forms![Game2].[ChkInID]..

=dlookup("[Score]","Score","ChkInID = '" & Forms![Game2].[ChkInID] & "' AND [GameID] = 1")

This is assuming Forms![Game2].[ChkInID] is a text value. If not remove the quote marks '

 

[Robecca]

Better ... the #Name? is gone however, all I have is an empty text box.

Could the fact that I have 4 entries for that same ChkInID be an issue? My data in that table can look like:

ScoreID ChkInID GameID LaneID Score
456 33 1 4 150
460 33 2 7 160
464 33 3 10 155
468 33 4 5 165

I feel like it's logical in my head but not working in the unbound text box.

 

[Robecca]

Sorry, the example did not come out formatted well.

 

[Isskint]

Could the fact that I have 4 entries for that same ChkInID be an issue?

No the 4 entries for the same ChkInID do not have a bearing as the where criteria in the Dlookup() says Where ChkInID = Forms![Game2].[ChkInID] AND [GameID] = 1, so it is only looking for the one record. ChkInID is a numeric value I would guess from the table you have included. Have you removed the quote marks as i suggested? For a numeric value you would use;

=dlookup("[Score]","Score","ChkInID = " & Forms![Game2].[ChkInID] & " AND [GameID] = 1")

NOT
=dlookup("[Score]","Score","ChkInID = '" & Forms![Game2].[ChkInID] & "' AND [GameID] = 1")

 

[Robecca]

Yes, I removed the single quote.

=DLookUp("[Score]","Score","ChkInID=" & [Forms]![Game2].[ChkInID] & " and [GameID] = 1")
returns empty cells for everyone

I even tried:
=DLookUp("Score","Score","ChkInID=" & "ChkInID" & " and GameID = 1")
returns David's Score for all bowlers

I'm missing something - simple!

 

[Isskint]

Can you attach your db and i will have a look?

 

[Robecca]

Here it is ...

 

[Isskint]

I have identified 2 problems The first is in the queries EnterGame1, EnterGame2 etc. You have a criteria of =Date() against the WkDate from CheckInBowler. This means the queries will only return data for the current date.
The other problem is the Name of the control on the subforms for the ScoreID. The controls are called ChkInID. So when you refer to the Field ChkInID in Table Scores, you are asking the Dlookup() to find a ChkInID = ScoreID, which is not going to happen.

EG David Warrick ScoreID on game1 = 1861, ScoreID on Game2 is 1862. The Dlookup() equates to Dlookup("[Score]","[Scores]","ChkInID= 1862 AND GameID = 1"). This combination does not exist. David Warrick's ChkInID is 466. If you change the control name for ScoreID and ADD ChkInID to the subforms, the Dlookup() will return the previous games score. Have a look at the amended attached. I have added lookups for games 3 and 4

 

[Galaxiom]

I built 4 tables (Game1 - Game4) to capture the 4 games we bowl each week.

Wait up. This whole thread has gone completely down the wrong path.

Firstly, the games should all be in one table, not four.

Only one form is necessary. The results of previous games would be displayed in a subform (Datasheet or Continuous Forms) whose RecordSource query selects the previous games.

The subform will display however many records is required without having multiple textboxes and custom ControlSources.

 

[Isskint]

Wait up. This whole thread has gone completely down the wrong path.

Firstly, the games should all be in one table, not four.

Only one form is necessary. The results of previous games would be displayed in a subform (Datasheet or Continuous Forms) whose RecordSource query selects the previous games.

The subform will display however many records is required without having multiple textboxes and custom ControlSources.

Quite correct Galaxiom. As the initial question was 'simple' in answer, i thought correct structure could wait until Robecca had the solution. A single table with a GameNo field, would resolve the issue and simplyfy this DB.

 

[Galaxiom]

i thought correct structure could wait until Robecca had the solution. A single table with a GameNo field, would resolve the issue and simplyfy this DB.

First rule of databases is always sort the data structure first. Otherwise a whole lot of time gets wasted on working around the problems caused by inappropriate structure and a lot of work on forms and code ultimately goes down the drain.

The DLookup complexity and need for multiple separate forms for each game can be completely avoided.

 

[Robecca]

Sorry, I mis-typed above. I have only one table (Score) with the fields listed in that first message.

I meant I had 4 forms for Game1-Game4 for 2 reasons. 1. I wasn't sure how to get them all in one form. 2. We change lanes after each game and I want the form to be organized by lane and then the position the bowler is in for that game. However, my brother would like to show previous games as we proceed.

So where do I go from here?

 

[Isskint]

So where do I go from here?

Have you checked my edited version of your DB? It resolves the initial problem.

I can not get on a 2010 machine right now. If you want to stream line your DB i will have a look at table structure later for you and suggest a few things, if you want.

 

[Robecca]

If I don't have the WkDate set with criteria of Date(), how do I get only that evening's games in my form?

I had been wondering if I needed ChkInID on my form Game2, is that what you meant?

I would love to have suggestions, most of my Access databases have been simple, this is the most complex one I've tried to build.

Thanks! Robecca

 

[Isskint]

That answers the WkDate criteria. It was only an issue whilst i was trying to figure out what was wrong. For your purposes it is correct.

ChkInID needs to be on each form. Have a look at the edited database i attached to my reply at post #11 - http://www.access-programmers.co.uk/forums/showpost.php?p=1223244&postcount=11

I will have a look over your DB l8r and see what i can suggest.

 

[Robecca]

Sorry for the delay ... first chance this evening to get on the computer. I don't know what I am to change the control name for ScoreID to (anything?) - it looks like it is still ScoreID on the amended dbase you attached.

EG David Warrick ScoreID on game1 = 1861, ScoreID on Game2 is 1862. The Dlookup() equates to Dlookup("[Score]","[Scores]","ChkInID= 1862 AND GameID = 1"). This combination does not exist. David Warrick's ChkInID is 466. If you change the control name for ScoreID and ADD ChkInID to the subforms, the Dlookup() will return the previous games score. Have a look at the amended attached. I have added lookups for games 3 and 4
​

I copied your syntax from Game3 and changed the 3 & 2 to 2 & 1 but I now get #Name? instead of the empty text box.

 

[Isskint]

I think i changed the name of the ScoreID textboxes to ScoreIDx, thats all it needs.

#Name error, just check all the control and field names are spelt correctly. It may be corrected when you change the name of the ScoreID textboxes.