By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,045 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,045 IT Pros & Developers. It's quick & easy.

history table

mseo
100+
P: 181
hi,
I have two tables:
1- customers that contains field named
Expand|Select|Wrap|Line Numbers
  1. customerid; autonumber; PK
  2. Companyname;string
  3. dateentered; date/time
  4. inactive; yes/no
  5. datemodified ;date/time
2- customerhistory:
Expand|Select|Wrap|Line Numbers
  1. customer_ID; number
  2. Inactive; yes/no
  3. datefrom; date/time
  4. dateto; date/time 
I have form for edit customers and its controls bound to table customers
I need when check the inactive checkbox to insert record in table customerhistory

any help would be appreciated
thank you in advance for any help
Aug 1 '10 #1

✓ answered by beacon

Hi mseo,

I would put code in the BeforeUpdate event for the checkbox and then use the INSERT SQL statement to put specific values in a table.

Since I don't know what values you want to insert into the table, I'm going to show you the structure and you can fill it in with the values you want:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Check0_BeforeUpdate()
  2.  
  3.     Dim strSQL as String
  4.  
  5.     strSQL = "INSERT INTO YourTable/Query(YourTable/QueryField1, YourTable/QueryField2, YourTable/QueryField3) " & _
  6.              "VALUES(" & Me.Control1 & ", " & Me.Control2 & ", " & Me.Control3 & ");" 'This assumes that all values are numeric. If you have a string or date, you will need to put a single quote or pound sign inside the quotes that surround the Me.Controls
  7.  
  8.     If Me.Check0.Value = True Then
  9.         DoCmd.RunSQL strSQL
  10.     End If
  11.  
  12. End Sub
  13.  
One thing that I probably should point out...the code I've provided doesn't check to see if the customer is already inactive and it doesn't do anything to reactivate the customer should the user accidentally check the check box. Hopefully this gets you started though and you can figure that other stuff out.

Share this Question
Share on Google+
4 Replies


beacon
100+
P: 579
Hi mseo,

I would put code in the BeforeUpdate event for the checkbox and then use the INSERT SQL statement to put specific values in a table.

Since I don't know what values you want to insert into the table, I'm going to show you the structure and you can fill it in with the values you want:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Check0_BeforeUpdate()
  2.  
  3.     Dim strSQL as String
  4.  
  5.     strSQL = "INSERT INTO YourTable/Query(YourTable/QueryField1, YourTable/QueryField2, YourTable/QueryField3) " & _
  6.              "VALUES(" & Me.Control1 & ", " & Me.Control2 & ", " & Me.Control3 & ");" 'This assumes that all values are numeric. If you have a string or date, you will need to put a single quote or pound sign inside the quotes that surround the Me.Controls
  7.  
  8.     If Me.Check0.Value = True Then
  9.         DoCmd.RunSQL strSQL
  10.     End If
  11.  
  12. End Sub
  13.  
One thing that I probably should point out...the code I've provided doesn't check to see if the customer is already inactive and it doesn't do anything to reactivate the customer should the user accidentally check the check box. Hopefully this gets you started though and you can figure that other stuff out.
Aug 1 '10 #2

mseo
100+
P: 181
@beacon
thank you beacon for your reply
In addition to you posted
I need to compare the last inserted record into table customerhistory for specific customerID if it is the same not to insert new record if the inactive control changed insert new record with new values

thank you very much
Aug 2 '10 #3

beacon
100+
P: 579
@mseo
You can use the DLookup function if you want to see if the specific customerID has been entered in the table/query. If you only want to check on the last record, you'll either need to use a SQL query using the LAST statement, DAO utilitizing the FindFirst - FindLast methods, or you can use DLookup in this case too in combination with a new query that looks up the latest ID in the table/query.

Once you've done the comparison, you can use the code I provided in my first post to insert the new record.
Aug 2 '10 #4

mseo
100+
P: 181
@beacon
thank you very much
Aug 2 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.