Access 2003 - Field from another database on form

Question

I am 'fixing up' an old Access Database, and the client requested that a DATE field be added to a form. Problem is, I have NEVER used Access before. I'm a SQL guy, and I build my own UI's. This forms thing is getting the better of me.

Ok - So I have two tables: tblQuestionairre QuestionairreID EventID blah blah blah

tblEvent EventID DateTime blah blah blah

Now, I am editing the frmQuestionairre (Questionairre Form). All the information from the Questionairre Table (tblQuestionairre) is here. Problem is, I need to add the DateTime field somewhere on this form so that the client can see when the questionairre was entered. As you can see, my linking item is EventID.

Try as I might, I cannot just "add" DateTime from the Event table using "expression builder". I need to load the correct DateTime for the current Questionairre that is loaded. Each Questionairre is linked to an Event.

How can I add this field to the Questionairre form? I keep getting a #Name? error, which is obviously because it doesn't know to link the two tables on EventID.. Ideas?

Edit: Yes this is a simple 1 to 1 relationship. Yes the field needs to be editable.

ANSWER: =DLookUp("[DateEntered]","tblEvent","EventID=" & Forms!frmQuestionnaire!EventID)

Edit: Now that I have it displaying, I cannot edit it!?

Answer 1

If I am reading the question correctly, you have a 1-1 relationship on EventID between tblQuestionairre and tblEvent?

If so, just open the property sheet for the form and edit the "record source" property on the "Data" tab so that it is based on a query that joins the two tables instead of just tblQuestionairre. You can even use the builder right from the property sheet to build the query.

Once you have that set, you should be able to just drop a new textbox on the form and set it's controlsource (again on the data tab of the property sheet) to the "DateTime" field.

Answer 2

The quickest way is to use the DLookup function in the OnCurrent event for the form. OnCurrent fires when a new record is loaded and DLookup performs a simple database lookup returning one value.

Answer 3

You need to change the RecordSource of the Form so that it is linked to a query joining the two tables rather than just one.

In your case, edit the RecordSource property and either use the designer to create the query or click on the SQL button to enter something like this:

SELECT tblQuestionaire.QuestionaireID, 
       tblQuestionaire.EventID,
       tblQuestionaire.blahblah,
       tblEvent.DateTime,
       tblEvent.blahblahblah
FROM tblQuestionaire 
INNER JOIN tblEvent 
      ON tblQuestionaire.EventID=tblEvent.EnventID;

This assumes that you have a strict 1 to 1 relationship between the tables, otherwise, use a LEFT JOIN if it is a 1 to [0,1] relationship.