Continuous Form Lookups

[adamburton]

I have a subform in continuous mode which has a list of people. I have created an unbound field which has a dlookup into a telephone number table.

What I want to happen is for each person in the list to have their telephone number displayed (not recorded - just displayed) on the form alongside their name. On a single form this works fine, but on a continuous form it displays the same phone number on all the rows (presumably the first person in the lists number).

Can anybody advise how I can get the dlookup working for each record in the continous form.

Thanks in advance,
Adam.

 

[Pauldohert]

Cant you include the telephone field in the query used as the recordsource and forget the Dlookup?

 

[adamburton]

Thanks, but still didnt work.

Thanks for your reply.

Ive tried to do that, but the phone table can contain multiple entries for each contact (daytime, evening etc). So to pull it into the query Ive created a query on the phone table to group each person ID and then take just the first number. Ive added this query into the query which is behind the subform. But now, this wont allow me to create a new record in the subform. So I dont think that ones going to work.

Am I missing something obvious here? Is there not a way to reference a specific cell within a continuous form or datasheet, rather than the whole field?

Thanks,
Adam.

 

[Pauldohert]

I don't think thats possible on a continous form using Dlookup,


Maybe you could remove the telephone field, and having a button to open a form showing the telephone numbers for that person. The advantage being, that the user gets the all the numbers rather than one.

Alternatively you could use a query to bring in the specific tel number you want to the subform, if this then means the subform is unable to create new records, you could have a popup to do that.

 

[adamburton]

Thanks Paul

Thanks for your suggestions Paul.

I already have built in the button taking users to the phone form, so I think I might just hope the user is ok with that.

But if not, I like the idea of the pop-up to add a new record.

Thanks again, that gives me a couple of options.

Cheers,
Adam.

 

[Bat17]

If you use the lookup function in the query rather than linking in the phone table then it should still be updatable

Peter

 

[Pauldohert]

A Dlookup in the query may make it really slow?

 

[Bat17]

It will depend on the data set, but no slower than if it were in the form.

Peter

 

[adamburton]

Problems

Ive just tried putting the dlookup in the query, but now the query is repeating the same phone number for all records. Im sure Ive got the dlookup correct, it was working for a single form. And I have just pasted across the exact string into the query, so am at a loss as to why it doesnt work.

If you have any quick suggestions then Id be grateful. If not, I will go with what I had and add a pop up for creating a new record, as Paul suggested.

kind regards,
Adam.

 

[Pauldohert]

Have you still got the Dlookup referencing the field on the form, you need to have it referencing a column in your query.

 

[adamburton]

Hi Paul,

The field name it is referencing is exactly the same in both.

Thanks,
Adam.

 

[Bat17]

It may help if you can post the SQL of the Query

Peter

 

[adamburton]

Here you go:

SELECT tblCourseContact.CourseID, tblCourseContact.ContactID, tblCourseContact.CourseFee, tblCourseContact.Materials, tblCourseContact.HutFee, tblCourseContact.DepositPaid, tblCourseContact.BalanceReceived, tblCourseContact.DiscountReason, tblCourseContact.SpecialDiet, tblCourseContact.ArrivalTime, tblCourseContact.AttendeeCancelled, tblCourseContact.OnWaitingList, DLookUp("[PhoneNo]","tblPhone","[ContactID]=[ContactID]") AS [Phone Lookup]
FROM tblCourseContact
WHERE (((tblCourseContact.OnWaitingList)=No))
ORDER BY tblCourseContact.ContactID;

 

[Bat17]

try
DLookUp("[PhoneNo]","tblPhone","[ContactID]=" & [ContactID]) AS [Phone Lookup]

Assuming that your ID is numerical, For text use

DLookUp("[PhoneNo]","tblPhone","[ContactID]='" & [ContactID] & "'") AS [Phone Lookup]



HTH

Peter

 

[adamburton]

Hi Peter,

Thanks for the suggestion. However, neither of these will even accept in the query. They both say invalid string (even after adding the closing bracket) - it seems to be the AS part thats causing the problem. Any ideas?

Thanks,
Adam.

 

[Bat17]

SELECT tblCourseContact.CourseID, tblCourseContact.ContactID, tblCourseContact.CourseFee, tblCourseContact.Materials, tblCourseContact.HutFee, tblCourseContact.DepositPaid, tblCourseContact.BalanceReceived, tblCourseContact.DiscountReason, tblCourseContact.SpecialDiet, tblCourseContact.ArrivalTime, tblCourseContact.AttendeeCancelled, tblCourseContact.OnWaitingList, DLookUp("[PhoneNo]","tblPhone","[ContactID]=" & [ContactID]) AS [Phone Lookup]
FROM tblCourseContact
WHERE (((tblCourseContact.OnWaitingList)=No))
ORDER BY tblCourseContact.ContactID;

or

SELECT tblCourseContact.CourseID, tblCourseContact.ContactID, tblCourseContact.CourseFee, tblCourseContact.Materials, tblCourseContact.HutFee, tblCourseContact.DepositPaid, tblCourseContact.BalanceReceived, tblCourseContact.DiscountReason, tblCourseContact.SpecialDiet, tblCourseContact.ArrivalTime, tblCourseContact.AttendeeCancelled, tblCourseContact.OnWaitingList, DLookUp("[PhoneNo]","tblPhone","[ContactID]='" & [ContactID] & "'") AS [Phone Lookup]
FROM tblCourseContact
WHERE (((tblCourseContact.OnWaitingList)=No))
ORDER BY tblCourseContact.ContactID;

Peter

 

[adamburton]

It Worked!!

BRILLIANT!!

It works a dream!!! Thanks so much for your help Peter.

I will remember how to do this for future situations.

kind regards,
Adam.