SQL Question - Creating 'Temporary Table'

[Phil1]

Hi all. This is my first post here. I kept getting directed here searching for my answers so figured I'd join to find my answers and contribute as I work on my current project.

My question is simply this...How do you have a 'SubForm/Subreport' Control update with any records that are similar to that of the current one you are creating.

To further explain what I'm trying to do and what is going wrong...
I am creating a Call logger database; what I'm aiming for currently is as I enter the details of the caller (Name, company name, contact number, etc) I would like a table at the bottom to update itself automatically with any records that exist in my main table that are similar to the currntly entered details, so that should a customer phone back again in the future I can 'LINK' Call Ids and keep a record of calls.

I've tried using the 'Query wizard', fiddling with the 'find duplicate records' example and referencing the Text boxes in my form. Combining this query with a subform/subreport control gave me what I was looking for, however when I changed any data I'm entering above they are being saved to the main table automatically before I want to (i.e. before any validation) (I understand why its doing this but what I'm looking for is a kind of "Temporary" recordset or Table to be used while my form is open for use in finding any aimilar records that exist, it should not save any data anywhere and I Dont want to be creating physical tables in my database when I can avoid it, I'd rather the table/recordset be in memory while being used.

I've read about "unbinding" the subform control so that it doesn't save but this hasn't worked for me. nor has using:

Cancel = False


Me.Undo

on the sub forms 'BeforeUpdate' Event for some reason.

Can anyone help me please?
Many thanks in advance,
Phil.

[June7]

Don't understand. The main form and subform have the same recordsource?

[Phil1]

Hi June7, thanks for your reply.

so far as I know they don't. perhaps someone else could confirm here but so far as I know a subform can be used to display the results of a query that is assigned to the 'DataSource' property of the subform or by creating another form (brand new form) and using the subform to display that new form as though you've opened it (you can edit the header, footer, detail and form etc) - kind of like a window into another form, so in effect you're opening and viewing two forms in one window/form (the one you're working on).

The problem I'm having is that I have a query built and it returns the results I want but when I enter say the caller's name and its the same as a previously entered name in the database the query updates the subform and returns the details for that existing customer ( i.e. telling me that they've phoned before). The problem is that once I enter any details and close the form without saving, its saving what i've entered automatically.

[Phil1]

A quick question to all which might help me....

Is it considered good practise to use temporary tables inside an SQL query and have this query ran everytime you change controls (to check entered data against the database).

Example code:

SELECT Tmp.CallerName, Tmp.CompanyName, Tmp.ContactNumber, Tmp.OtherNumber, Tmp.Email
FROM TblPhoneCall AS Tmp
WHERE .....

or would it be better coding my SQL inside of VBA?
Also after this query is ran, does the 'Tmp' table remain or is it erased and recreated when this SQL query is ran again?

[June7]

I don't understand what you are doing: "Is it considered good practise to use temporary tables inside an SQL query and have this query ran everytime you change controls (to check entered data against the database)." Why does entered data have to be 'checked'?

'Tmp' is not a table, it is just an alias name in the query that pulls data from TblPhoneCall. That name exists nowhere except in that query unless you save that query with name Tmp. I see no reason for the 'Tmp' alias if not doing self-joins or subqueries.

Subform/subreport is created by placing a subform container control on another form. That container has a SourceObject property. The SourceObject can be a table, query, form, report.

Do you want to provide the project for analysis?

[Phil1]

I'd rather not, I believe it could be answered on here once we're all singing off the same sheet so to speak. :-)

Basically what I'm trying to do is...

I enter caller details and any existing records that are similar to the details entered are shown in a table at the bottom.

Thanks June7 for your patience.

[June7]

Form/subform arrangement. Main form bound to caller info table, subform bound to the call log table. Select or enter basic caller info on main form, call details in the subform. http://office.microsoft.com/en-us/ac...010098674.aspx

[Phil1]

Thanks June7 that info is useful.
The subForm was proving pretty tricky so I've decided to use a simple List Box rather than a subform, this seems to be a simpler option.

I'm having another problem though, I'm using this SQL code in VBA to return any records that start with the value entered in the name text box. but its returning an error
Can anyone point out where its going wrong?

The Error is:

Run-time error '3061':
Too few parameters. Expected 1.

The VBA code i'm using is:

sSQL = "SELECT TblPhoneCall.CallerName, TblPhoneCall.CompanyName, TblPhoneCall.DateTime, TblWelcomePacks.ID " & _
"FROM TblPhoneCall INNER JOIN TblWelcomePacks ON TblPhoneCall.ID = TblWelcomePacks.ID " & _
"WHERE " & EdCallerName.Value & " LIKE 'TblPhoneCall.CallerName*' " & _
"ORDER BY TblPhoneCall.CallerName;"

Debug.Print is returning the following...

SELECT TblPhoneCall.CallerName, TblPhoneCall.CompanyName, TblPhoneCall.DateTime, TblWelcomePacks.ID FROM TblPhoneCall INNER JOIN TblWelcomePacks ON TblPhoneCall.ID = TblWelcomePacks.ID WHERE John LIKE 'TblPhoneCall.CallerName*' ORDER BY TblPhoneCall.CallerName;


There is a record with the name 'John Smith'

Thanks.

[June7]

You have the wildcard with wrong element of the query. Don't put field name within apostrophes.

Try:

"WHERE CallerName LIKE '" & Me.EdCallerName & "*'"

or

"WHERE CallerName LIKE """ & Me.EdCallerName & "*"""

[Phil1]

the first one worked June7. Thank you very much.

So for future reference, never use wild cards in VBA (or ever in fact) next to Table field names?

Thanks again

[June7]

That is the usual situation. User inputs a value or partial value and the wildcard 'fills in' so can match against existing data.

[Phil1]

That makes sense. Thanks again June7.