Too many popups on query run (1 Viewer)

[MaryamF]

to run this line, I get too many popups :
DoCmd.OpenQuery "QryMonthyPays"

"QryMonthyPays" is a Make Table query.
...run the query....
....is about to make a table....
.....is about to delete the existing table....
...

How can I avoid all these popups ? Anything that I could change on Options?
If not, Can I replace this query with something else in VBA?

I need to find a person based on a code which user enters, ... get three parts of data from 3 different tables.
Code and name from People Table.
Business Unit and address from BU Table.
Monthly pay from Payments Table.
and shows all these on a form.

if the user has entered the wrong code gets a "Wrong Code" message.

Thanks

 

[arnelgp]

you can use a Form to collect all your Query parameters and only execute the query when all the parameters are supplied.
then use the Form's textbox as your parameter.

 

[pbaldy]

How can I avoid all these popups ? Anything that I could change on Options?

You can change an option but that only fixes it on your computer.

SQL Warnings

 

[Pat Hartman]

I suggest using the .Execute method of DAO rather than Access methods such as OpenQuery

There are options to turn of warning messages but they are EXTREMELY DANGEROUS so you need to be careful if you turn warnings off. My solution is to use two macros. WarningsOff and warningsOn. The key is that in the Off macro, I turn the warnings off but the hourglass on and the On macro does the opposite. This will not even be noticed if you run them WarningsOff, OpenQuery, WarningsOn. However if you stop your code while you are debugging while warnings are off, the hourglass is left on and that is sufficiently annoying such that you are unlikely to want to leave it that way so you just click on the on macro and that sets warnings back on and the hourglass off.

But, using the execute method, you code looks something like:

Dim db AS DAO.Database
Dim qd AS DAO.Querydef
    Set db = CurrentDB()
    Set qd = db.Querydefs!myqueryname
        qd.Parameter!myparmname = Me.Somecontrolname
        qd.Parameter!myparm2name = Me.Someothercontrolname
        qd.Execute

 

[plog]

I need to find a person based on a code which user enters, ... get three parts of data from 3 different tables...and shows all these on a form.

Displaying data on a form doesn't require making tables, deleting data or any other action on data.

All you need is to use a Dlookup to retrieve data from a table:

MS Access: DLookup Function

This MSAccess tutorial explains how to use the Access DLookup function with syntax and examples. The Microsoft Access DLookup function returns a value from an Access table (or domain).

www.techonthenet.com

 

[ebs17]

.,..and shows all these on a form

The simple and usual way would be to create a query across the three tables - hopefully there are relationships that make this easy and sensible.
You display the query with initially all data (alternatively without data with filter False) in a bound form or a list field.
Now this query is filtered for desired content. Filtering is THE standard task in the database world.

Starting to display data with a make table query seems quite unstructured because it creates redundant data. Is there a special justification for this approach?

 

[MaryamF]

Private Sub TXTSSN_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
DoCmd.OpenQuery "QryMonthyPays"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM TblMonthlyPaysTemp WHERE SSN='" & Me.TXTSSN & " ' ")

With rst
If .EOF Then

MsgBox ("*SSN Doesn't Exist.*")

Else
MsgBox "SSN: " & Me.TXTSSN & " Already Exists and Belongs to:" & vbCrLf & _
!FullName & " " & !PhoneNumber, vbExclamation
Me.FullName = !FullName
Me.FullName.Enabled = False
Me.PhoneNumber = !PhoneNumber
Me.PhoneNumber.Enabled = False
Me.BUID = !BUID
Me.BUID.Enabled = False
Me.BUAddress = !BUAdresSt
Me.BUAddress.Enabled = False
Me.PayAmount = !MonthlyCharge
Me.PayAmount.Enabled = False
Me.DateOccup = !OccupationDate
Me.DateOccup.Enabled = False
.Bookmark = .LastModified

End If
End With
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

-----------------------------------------
How can I replace docmd.query with execute?
QryMonthyPays is a Make Table Query: TblMonthlyPaysTemp

 

[MaryamF]

The simple and usual way would be to create a query across the three tables - hopefully there are relationships that make this easy and sensible.
You display the query with initially all data (alternatively without data with filter False) in a bound form or a list field.
Now this query is filtered for desired content. Filtering is THE standard task in the database world.

Starting to display data with a make table query seems quite unstructured because it creates redundant data. Is there a special justification for this approach?

No there is no special justification for this approach. I don't know any better.

 

[Pat Hartman]

You seem to be using an unbound form for some reason. That just forces you to write unnecessary VBA. Access handles populating forms if you just bind the form to a query or table.