How to use the count returned by total query

[wallymeister]

I have a query that is comparing 2 tables (Models & Parts) [ModelID] to
[ModelID] and only shows records in Models table that don't have a matching
record in Parts table. Here is the SQL

SELECT Models.ModelID
FROM Models LEFT JOIN Parts ON Models.ModelID = Parts.ModelID
WHERE (((Parts.ModelID) Is Null));

Then I have a Totals qry that Counts the records found in above qry.

I want to;
When user close this form (onclose event) place code that runs Totals qry's
and then asks the user if new records created were intentional or not. Then,
either run a delete qry or run an append qry that adds the appropriate
records to the parts table. I have the append/delete queries working (just
not placed in code to run behind form. I don't know how to use the value
returned by Totals qry to display a message that asks user if (e.g) 3 records
have been added do you wish to proceed etc, etc)

This is all to keep the tables from having orphan records, if indeed a new
model is added then there must be new matching parts records.

Hope I have explained well enough for someone to help. I've forgot so much
about Access than I'd like to admit.

Thanks,
wally

 

[wallymeister]

Well, I have solved my problem, and as I suspected, it wasn't near as
complicated as I was trying to make it.

Here is what I did;

Dim rs as Recordset
Set rs = Currentdb.OpenRecordset("Models Without Matching Parts",
dbOpenSnapshot)
If Not rs.EOF Then rs.MoveLast
CountTotal = rs.RecordCount

Now I just use CountTotal in my MsgBox to warn user of how many records they
are trying to add.

This gives me what wanted without using the other qry that is a totals query
with field named CountOfModelID that I was trying to pull that value out of
the query. Easier to just do the above. However, If I needed that value of
CountOfModelID because it wasn't just counting records found, say counting
something else within the ModelID field. How would I do that? Still would
like to know if someone has time to help with that.
Any help is appreciated, greatly
wally