Quote:
Originally Posted by Rabbit
You don't have a space between your table name and your SET keyword. And you don't have a space between your Date() function and your WHERE keyword.
But why are you doing it this way? I don't know how your tables are set up and how your forms are set up but with a normalized table design and cascading relationships set up, there are very few situations where you need to insert and update records manually.
Thank you very much for your quick reply, Rabbit!
I tried to insert the space as you told me, both before and after, but did not work... Other SQL statement I wrote doesn't have space, but they work fine.
This database that I am working on is kind of complicated... There are so many sub forms attached to the main form.
People who want me to work on this had this database for about 2 years, and this old database has too much problems. The guy who made this old database quit long time ago, so I am trying to make new forms and queries using the tables from the old one. The people do not want me to change most of the functions and designs, especially those automated insertion and update.
Example of the problems with old database are, it shows error message all the time, some people cannot get in to the database, some date fields always shows the way wrong date, like 2004, etc. All the fields in the form are not made using wisard. The guy who made this database created labels or text box for every single field in the form, write VBA and SQL code for all of them, even though he did not have to do so by using wizard. So I am trying to make the database as simple as I can by using wizards and to get rid of unnecessary codes.
Here is how the main and sub forms looks like (I can e-mail you screen shot of this form if you would like):
Main form=frmClaim: (based on table claim)
-------------------------------------------------------------------------------------------------------------------
<1st tab in the form: General Information>
(All fields are combo box except for claimID)
-claimID,
-status,
-claim reasons,
-claimant id (below this, there is a subform to show claimant's information such as address, phone# based on claimant id),
-carrier (below this, there is a same kind of subform as claimant id),
-origin state,
-origin city,
-origin (state, city, origin are combo boxes. It shows a list based on what an user select in previous box. For example, if user selected "CA" in state, it shows cities we have in city combo box. Below this, there is a same kind of subform as claimant id),
-destination state,
-destination city,
-destination (same kind of combo boxes as origin. below this, there is a same kind of subform as claimant id)
*Next to each field except for claimID, there are buttons where a user can click to add values if the user doesn't see the value in combo box.
---------------------------------------------------------------------------------------------------------------------
<2nd tab in the form: Returns and Activities>
Reference Numbers sub form (table view/ based on table claim_refnum and claim_refnum type):
-Reference Description (combo box), Refnum Value, InsertUser, UpdateUser, (hidden fields: claimID, Refnum)
Activities subform (table view / based on table claim_activity and claim_activity_type)
-Date, Activity Description (combo box), Insert User, and Update User (hidden fields: ClaimID, ActivityID)
*Next to both sub forms, there are buttons where a user can click to add values for Reference Description and Activity Description, if the user doesn't see them in combo box.
*Activity Description "Date Opened" is inserted to the record with date when an user create new record.
*Activity Description "Date Mailed" is inserted to the record with date when an user first print sthe report.
---------------------------------------------------------------------------------------------------------------------
<3rd tab in the form: Products/ Transactions>
Products subform (table view / based on table claim_product and product)
-ProductID (combo box), Description (shows description based on ProductID), Units, Wt/Unit, Cost, Qty, Total Wt (calclates total weight), Total Cost (calclates Cost * Qty for each line of product), (hidden fields are ClaimID)
Total Products subform (based on query qryClaimProductsTotal)
-Total Product Cost (calclates all the products cost from subform Products)
Transaction subform (table view / based on table claim_transaction and claim_transaction_type)
-Date, Transaction Type, Date Check Received (this field is not filled unless the user receives check and transaction type is related to check receipt), Amount (when customer pays, it's inserted as -$100 (e.g.)), CheckNo, InsertUser, UpdateUser
Total Balance subform (based on query qryTotalBalance)
-Total Balance (it calclates amount in transaction subform and shows balance.)
*Data in transaction subform is related to transaction field in the report, so Total Product Cost must be inserted.
*Transaction Type = "Total Product Costs" is inserted when the user first create the record. If there is no products in Products subform, then it inserts amount as zero.
*There are buttons for the user to add products and transaction type when the user doesnt see it on combo box.
----------------------------------------------------------------------------------------------------------------------
<4th tab in the form: Comments>
External Coments Subform (tabular view / based on table claim_comment)
-Date, Insert User, Comment
Internal Memo (this field is attached to main form, frmClaim, based on table claim)
----------------------------------------------------------------------------------------------------------------------
<In Header: enable the user to search reference number values in subform reference numbers.>
Reference number filter
-Reference Number Type (combo box)
-Reference Number
I hope this helps. Sorry about the long list. I wish I can make this explanation shorter, but I couldn’t make any shorter than this. .
