Form field from two tables

[AndyCompanyZ]

I have a textbox on a form that displays the venueroomID of a venue room that has been selected prior and stored in a tblEvent. This works fine and is on the form so that the user willknow if a room has already been selected and therefore won't select another. I now need to display the actual room name which is stored in another table tblVenueRooms. I am having a problem working out how to get this to show I tried a query :

SELECT tblVenueRooms.VenueRoom
FROM tblEvent INNER JOIN tblVenueRooms ON tblEvent.VenueRoomID = tblVenueRooms.VenueRoomID
WHERE (((tblEvent.EventID)=[frmEvent].[EventID]));

but this brings up #NAME in the field. I'm sure this is simple to do but I'm having problems today with my brain not working. can anyone ppint me in the direction I need to go please.

 

[JANR]

SELECT tblVenueRooms.VenueRoom
FROM tblEvent INNER JOIN tblVenueRooms ON tblEvent.VenueRoomID = tblVenueRooms.VenueRoomID
WHERE (((tblEvent.EventID[COLOR=black])=[COLOR=red]Forms[/COLOR]![[/COLOR]frmEvent].[EventID]));

JR

 

[AndyCompanyZ]

No sorry that didn't get it
Thanks though

 

[JANR]

Obs diden't see the last error use ! (bangs) and not periode when refrencing other objects than fields in the query:

WHERE (((tblEvent.EventID)=Forms![frmEvent]![EventID]));

JR

 

[AndyCompanyZ]

No still not working just #NAME. Is my logic correct on the query

 

[JANR]

Can't see anything wrong with the SQL. Is the form frmEvent open and is a value selected in the control [EventID]?

To be sure use the wizzard to locate the control on the form that the where-clause points to.

1. Put the cursor in the criterarow
2. Start the wizzard
3. select Forms
4. select Loaded Forms
5. Locate the control that displays the EventID

JR

 

[AndyCompanyZ]

I did that before and the form is open with an eventid

 

[spikepl]

A text box cannot be set to a record source

 

[AndyCompanyZ]

So I can't do this at all then.

 

[missinglinq]

Sure you can! As spikepl said, you can't set a Control Source of a Textbox to a Query or SQL Statement.

For this kind of thing you need to use the DLookup function. The syntax varies depending on the Datatype of the Field used in the WHERE Clause:

'If VenueRoomID is Numeric

= DLookup("VenueRoom", "tblVenueRooms", "[VenueRoomID] = " & Me.txtVenueRoomID)

'If VenueRoomID is Text

 = DLookup("VenueRoom", "tblVenueRooms", "[VenueRoomID] = '" & Me.txtVenueRoomID & "'")

Linq ;0)>

 

[AndyCompanyZ]

Thanks I tried this:
=DLookUp("VenueRoom "," tblVenueRooms ","[VenueRoomID] = '" & [Me].[VenueRoomID] & "'") but it gives me a #NAME Error.

 

[missinglinq]

Thanks I tried this:
=DLookUp("VenueRoom "," tblVenueRooms ","[VenueRoomID] = '" & [Me].[VenueRoomID] & "'") but it gives me a #NAME Error.

First off, remove the Square Brackets from around [Me] and change

"VenueRoom "

to

"VenueRoom"

In other words, remove that extra space after VenueRoom and before the Quotation Mark. Do the same getting rid of the spaces before and after tblVenueRooms and see if that does it.

Both of those things need to be done, in any case, but if that doesn't resolve the issue, check that all of the names match your actual names.

Linq ;0)>

 

[AndyCompanyZ]

I have changed it to =DLookUp("VenueRoom"," tblVenueRoom ","[VenueRoomID] = '" & Me.[VenueRoomID] & "'") but it causes a circular reference to itself.

 

[missinglinq]

...Do the same getting rid of the spaces before and after tblVenueRooms...

You still haven't gotten rid of the spaces before and after tblVenueRoom! To the Access Gnomes

" tblVenueRoom "

is not the same as

"tblVenueRoom"

And leave off the Square Brackets around Me.[VenueRoomID]. The only time you need to do this with a Control Name is if it has spaces in it.

Try

=DLookUp("VenueRoom","tblVenueRoom","[VenueRoomID] = '" & Me.VenueRoomID & "'")

If this still bombs

• Is tblVenueRoom the Table that holds the room description?
• Is VenueRoom the name of the Field holding the description?
• Is VenueRoomID the name of the Textbox on your current Form holding the ID?
• Is VenueRoomID also the name of the Field in the tblVenueRoomthe Table for the ID?

Linq ;0)>