Too many popups on query run

[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

www.baldyweb.com

 

[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.