Results 1 to 11 of 11
  1. #1
    visidro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2013
    Posts
    23

    Help with access code (macro?) please

    I am fairly new to access and need help writting code to generate records.

    We have a database with customers that are billed monthly and would like to create code (macro?) to write records to a table with data and values obtained from another table.

    I would like to run a program that obtains: the "name" of the customer, the "amount", the "subject" they are studying IF the customer is "active" and write those records to a table for later use.

    The existing table with the data is called "Students Database Table"
    The table to write the new records to is called "Student Payments"

    Any help would be much appreciated
    thank you


    victor

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Why write records as opposed to simply filtering?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    visidro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2013
    Posts
    23
    I need to create a new record every time a "customer" makes a payment. there are over 700 "customers" and I would like it to be automated.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Try something like:

    CurrentDb.Execute "INSERT INTO Payments(field1, field2, field3) SELECT field1, field2, field3 FROM Students WHERE Status='Active';"

    The real trick is figuring out what event (such as button Click) to run this in and preventing user error that repeatedly runs the code or forgets to run at all.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    visidro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2013
    Posts
    23
    It works great thank you.
    I would like to add the date when the button is clicked to a field in the Payments table for each record.

    Can you suggest the code please.
    victor

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Try:

    CurrentDb.Execute "INSERT INTO Payments(field1, field2, field3, datefield) SELECT field1, field2, field3, #" & Date() & "# AS TodayDate FROM Students WHERE Status='Active';"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    visidro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2013
    Posts
    23
    Great help thank you.

    If I wanted to make this button ask a Date question before inserting the records???
    In other words, only insert records with a date greater/older than say 15/03/2013
    Could you help with that code please

    victor

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Again, concatenate the variable parameter.

    WHERE Status='Active' AND [your date fieldname]>#" & Date() "#;"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    visidro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2013
    Posts
    23
    I meant that I would like to ask the operator which date to look at records from.

    Perhaps have a message box asking for a date??/

    This is the code I have working:

    CurrentDb.Execute "INSERT INTO Payments(firstname, lastname, amount, program, classtype, pdate) SELECT firstname, lastname, installments, program, classtype, Date() AS TodayDate FROM [Students Database Table] WHERE Active=0 AND [enrollmentdate]> date supplied by operator???????

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Instead of Date() in the WHERE clause, could be reference to textbox on form or an InputBox. The concatenation syntax would not change, just the reference.

    Do not advise an InputBox. Too hard to validate user input. A date picker on form or at least a textbox with input masking to force a valid date entry.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    visidro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2013
    Posts
    23
    Great help, all works
    thank you very much for your help
    victor

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA code or Access Macro
    By CSGabriel in forum Access
    Replies: 1
    Last Post: 07-27-2012, 09:08 AM
  2. VBA code/Macro help please.
    By Davidyam in forum Access
    Replies: 1
    Last Post: 02-26-2012, 09:59 PM
  3. Question about some code in a macro
    By AudiA4_20T in forum Programming
    Replies: 2
    Last Post: 07-11-2011, 08:16 AM
  4. Run a Embedded Macro from VBA Code
    By sabre1 in forum Programming
    Replies: 3
    Last Post: 02-25-2011, 10:26 AM
  5. how to create a macro or code to...
    By Eaglezinha in forum Access
    Replies: 1
    Last Post: 10-20-2008, 04:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums