Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2020
    Posts
    18

    Creating a record with a button using VBA

    I am very new to VBA. I can follow along pretty well but I struggle with the syntax when writing code. I have a split form with a button. Upon clicking the button, I would like to set certain values in that row to a new row in another table. I know that "Me" is important but am unsure what it does. I've also read several posts talking about a DOA? I'll paste my code below. I'm also having trouble with spaces in my variable names and am unsure how to get around that.

    Thank you so much for your help in advance.


    Private Sub Consume_Click()
    Qty = [Qty] - [Qty Pulled]



    DoCmd.GoToRecord , , acNewRec '(I need to get this to a "Consume" table)
    Me.[ID] = C_ID
    Me.[Name] = C_Name
    Me.[Part Number] = C_Part Number
    Me.[Program] = C_Program
    Me.[Lot Number] = C_Lot Number
    Me.[Qty] = C_Qty
    Me.[Run Number] = C_Run Number

    Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

    Error_Handler:
    MsgBox "Cannot have Qty < 0"
    Resume Error_Handler_Exit

    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    The ME is shorthand for the form name. You Don't really need it,but it does let you know THIS is a local form control.
    dont put spaces in field names,nor table names. Just don't use spaces in names.

    You don't need DAO,everything you need is in the form or CurrentDB.(object name).

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I ALWAYS use "ME." to help me know that the object is a control on a form.
    IF (that is a big if ) you must have spaces in objects in VBA, you must enclose the variable name in square brackets -->> [C_Part Number], otherwise you get an error.

    Would you explain more about this:
    Quote Originally Posted by cassandragubler@gmail.com View Post
    Upon clicking the button, I would like to set certain values in that row to a new row in another table.
    Your code is not adding a new record to a different table, it is adding a new record to the table/query that is bound to the form.
    I am not sure where you are headed, but I would suggest using an Append query (SQL code in the VBA) to add the new record (and data) to the other table.


    You have a field in a table that is named "NAME". "Name" is a reserved word in Access and shouldn't be used for object names.
    Plus it is not very descriptive. Name of WHAT? A company, employee, city, state, a project, etc.

    What field type is the field "ID"?


    Maybe you would post a copy of your dB for analysis?

  4. #4
    Join Date
    Feb 2020
    Posts
    18
    I would post the database cause that would make it a ton easier but it has classified parts in it. So that's the hard thing. A bunch of engineers asked if I knew how to make a database using Access. I told them I had never used Access and they said, "Great, can you make this database for us?" So that's how this is going.

    So, if I understand you both correctly, "Me" is talking about whatever form I am currently in?

    Ex: Say I have a part named "Hammer". Me.[Name] will return "Hammer" correct? In that case, I think I have my code backwards because I need it to more like "C_Name = Me.[Name]". I know I need to reference the table where "C_Name" is but I'm not sure how to do that.

    But speaking of "Name", I will change that so as not to confuse it with the reserved word. ID is a number that each part has. They gave me a bunch of columns to include.

    As far as the button, say I have a part named "Hammers" with "Qty = 5" and a "Run Number = 123456". I have a "Qty Pulled" form that gives the history of all the parts that we have pulled. So, from the original form, the engineers highlight a row and input the quantity that needs to be pulled and the run number they are using it for. Upon clicking that button, the quantity automatically updates (I have that part figured out), but I need certain information to go to the history form so that they can pull it up later and see all the parts they have pulled, etc. Does that make more sense?

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,520
    ....but it has classified parts in it. ....
    So make a copy. Remove ALL sensitive data and add a few fictitious records to illustrate your problem
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You really need to be a lot more specific when giving examples and asking questions. Remember, we can't see your dB (unless it is still the "Hurricane_Mesa_Parts_Inventory - VC"?)
    It is like you giving me directions by telling me to turn left, then take 2 rights and a left - but I don't know where to start from or the street names.


    Here is an example of an append query (in code) to insert a record into a table ("tblAuditTrail") where the form is bound to table "InventoryTable"
    Code:
    Private Sub Consume_Click()
        Dim sSQL As String
    
        sSQL = "INSERT INTO tblAuditTrail ( C_Part Number], C_Program, C_Lot Number] )"
        sSQL = sSQL & " VALUES ( " & Me.[Part Number] & ", " & Me.[Program] & ", " & Me.[Lot Number] & ")"
    
        CurrentDb.Execute sSQL, dbFailOnError
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    
    Error_Handler:
        MsgBox "Cannot have Qty < 0"
        Resume Error_Handler_Exit
    
    End Sub
    So it is important to know WHERE the values you want to insert come from, the name of the table you want to add the record to and the names of the fields in that table.
    If you feel you can't post the dB, maybe an image of the relationship window or an image of the table to get the field names......

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

Similar Threads

  1. Refresh Button creating a new record?
    By mejia.j88 in forum Programming
    Replies: 9
    Last Post: 05-31-2012, 11:27 AM
  2. Replies: 3
    Last Post: 03-07-2012, 09:49 AM
  3. Replies: 2
    Last Post: 02-21-2012, 01:35 PM
  4. Replies: 3
    Last Post: 02-23-2010, 06:32 PM
  5. Replies: 3
    Last Post: 01-14-2010, 08:32 AM

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