text box

[Parula]

If I have a form with multiple columns and rows in the first column and first row I would like the user to enter in a zip code in the next column I would like the total of all the people in that zip code. I have created a query that does that for me but I do not know how to incorporate that query in to the form. The query pulls its information form the two tables that I have created one is called the customer and other one is called the transaction. in the transaction query i have field called types (Which has 6 different types of gift certificates Anniversary, birthday, thank you, frequent visitor, new invites, and large party) Once the user get the total number of customers in that zip code, in the 3rd column I would like the user to be able enter in amount he/she wants to send that number to a table for that zip code.

 

[rbowes]

I would use a recordset to search the database with your sql statement and then place the recordset's recordcount into the field

Private sub Zipcode_afterupdate()
dim rs as new adodb.recordset
dim cn as new adodb.connection
dim strsql as string

rs.cursorlocation = adUseClient
Set cn = CurrentProject.Connection 'assuming the tables are local'

'I am assuming that the sql statement would look something like this '
strsql = "Select custmomers.Person_ID" & _
"FROM Customers, transaction" & _
" WHERE Customers.Person_ID = transaction.Person_ID" & _
" AND Customers.Zipcode = ' & me.Zipcode & '"

rs.Open strsql, cn

with rs
if .BOF and .EOF then
Me.Zipcode_Total = 0
set rs = nothing
exit sub
end if
me.Zipcode_Total = rs.Recordcount
end with
me.repaint
set rs = nothing
set cn = nothing
end sub

That should work. Let me know.

Bob