Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    New records from old records (97)

    I have a bill of material for each model tool. Each model has many records for each part that make up the model. If I want to add a new model and it is close to an existing model, is the best way to accomplish this to run a select query based on the model that it is close to, then make a table from this query, make my changes in the new table (like new model number), then append this new table to the old table and delete the new table? Thanks for looking. John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New records from old records (97)

    I wouldn't go to the trouble of creating and deleting new tables. Instead, use code to duplicate a model completely, or only those bits that are likely to stay the same.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: New records from old records (97)

    Hi Hans -
    Welcome Back!!
    Coding is still new to me. Are you talking about a SQL code or opening up an intermediate window and run code from there?
    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New records from old records (97)

    John,

    I assume that you have a form to display/edit the records. You could put a command button on the form. When clicked, it will execute VBA code (which could involve running an SQL statement) to duplicate a record or records. In order to be able to give more specific assistance, we would need to know more about the structure of your database, and about what exactly you want to do:
    - Do you have one table, or a main table (models) plus a sub table (parts)?
    - When you duplicate a model, should all records corresponding to the parts be duplicated, or just one, or something in between?
    - Do you want all fields of the record(s) to be duplicated, or some of them?

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New records from old records (97)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    Hi John

    The following link to The Access Web - Bill of Materials may give you an approach:

    http://www.mvps.org/access/modules/mdl0027.htm

    HTH

    John

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: New records from old records (97)

    Hans -
    I have one table that has fields for model, part number, and order factor. When I want to duplicate a model, I would want to duplicate all the records for a model then I would go and edit the records that change for this new model. The model field would have to change for all these duplicate records to a new model. In editing the new records, a record might need to be deleted (part is not used on this new model), or order factor might need to change.
    Thanks for your help. John

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: New records from old records (97)

    Hi John
    By your nic it looks like I have my own personal support now. LOL
    I will download the database and study it to see how I might be able to use it. Thanks for your reply.
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New records from old records (97)

    If you are in a position to do so, I would recommend splitting your table into two tables:

    1. A Models table, with an AutoNumber field ModelID as primary key, plus if necessary other fields that are model-related.
    2. A Parts table, with a Numeric (Long Integer) field ModelID that is linked to the ModelID field in the Models table.

    You would add a new record to the Models table, copy the values of the current record into it, and store the new ModelID value in a variable. Then you would copy each corresponding record in the Parts table, replacing the old ModelID by the new value.

    If that is not feasible:

    What type is the field that identifies the model?
    How should this field be populated for the duplicate? Should it be assigned automatically, or should the user be prompted for a value?

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: New records from old records (97)

    The model is made of alphanumeric characters so a autonumber model field is not feasible. The letters and numbers that make up the model mean something and they have no order to them.
    I do have a parts table. Each part number is unique in this table and is a primary key. It has fields of part number, description, unit of measure, and cost and has a relationship to the model table by part number.
    I don't think I have said it before but I wanted to keep the old model number, not replace it with the new model. If I understood your post, the old model was going to be replaced by the new model.
    The user would need to be prompted for the new model for an alphanumeric text field.
    Thanks again. John

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New records from old records (97)

    This reply is solely about the structure.

    If I would set up such a database, I would use an AutoNumber field in the model table as primary key, and put a unique key on the alphanumeric model field. I would use three tables:

    tblModels with just the model info
    tblParts with the part info
    tblModelParts as an intermediary table that keeps track of the many-to-many relationship between models and parts.

    See screenshot of relationships.
    Attached Images Attached Images
    • File Type: png x.PNG (4.4 KB, 0 views)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New records from old records (97)

    This reply is about duplicating a model in the table structure you described. Create a small unbound form. Put a combo box, a text box and two command buttons on it.

    The combo box cbxModel will display the unique model identifiers. Its row source can be the name of a query that returns unique model identifiers, or an SQL string

    SELECT DISTINCT Model FROM tblModels

    where tblModels is the name of the table and Model is the name of the identifying field. The user will select an existing model from the combo box, and enter a new model identifyer in the text box txtNewModel.

    The first command button (named cmdOK and with caption OK) will create the new records, the second one (named cmdCancel and with caption Cancel) will just close the form. The On Click code for the OK button looks as follows; of course, you must substitute the actual names of the table and fields you are using.

    Private Sub cmdOK_Click()
    Dim strSQL As String

    If IsNull(Me.txtNewModel) Then
    MsgBox "Please enter a new model", vbExclamation
    Me.txtNewModel.SetFocus
    Exit Sub
    End If

    strSQL = "INSERT INTO tblModels (Model, PartNumber, OrderFactor) " & _
    "SELECT " & Chr(34) & Me.txtNewModel & Chr(34) & _
    ", PartNumber, OrderFactor FROM tblModels WHERE " & _
    "Model = " & Chr(34) & Me.cbxModel & Chr(34)

    DoCmd.RunSQL strSQL
    End Sub

    The On Click code for the Cancel button is

    Private Sub cmdCancel_Click()
    DoCmd.Close acForm, Me.Name, acSaveNo
    End Sub

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: New records from old records (97)

    Hi Hans -
    I got the combo box and the cancel button to work. I get an error with the OK button. Runtime error 3075. Syntax error (missing operator) in query expression 'Mod = "123"'. The debug window has the DoCmd.RunSQL line highlighted. If I leave the txtNewModel field blank, it works as expected.
    I made a table called test.
    Fields = Mod, Jn, OF
    I put 1 record in table test, it has the mod as 123.

    Private Sub cmdOK_Click()
    Dim strSQL As String

    If IsNull(Me.txtNewModel) Then
    MsgBox "Please enter a new model", vbExclamation
    Me.txtNewModel.SetFocus
    Exit Sub
    End If

    strSQL = "INSERT INTO test (Mod, JN, OF) " & _
    "SELECT " & Chr(34) & Me.txtNewModel & Chr(34) & _
    ", JN, OF FROM test WHERE " & _
    "Mod = " & Chr(34) & Me.cbxModel & Chr(34)

    DoCmd.RunSQL strSQL

    End Sub

    I am not sure what to add or change to make it work. Sorry. I have looked at it and looked at it again.
    Thanks for your help again. Best Wishes, John

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New records from old records (97)

    I think you're running into problems here because Mod is also a VBA function: it is the remainder after integer division, for example 17 mod 5 = 2. Try placing the field names between square brackets [ and ]:

    strSQL = "INSERT INTO test ([Mod], [JN], [OF] " & _
    "SELECT " & Chr(34) & Me.txtNewModel & Chr(34) & _
    ", [JN], [OF] FROM test WHERE " & _
    "[Mod] = " & Chr(34) & Me.cbxModel & Chr(34)

  14. #14
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: New records from old records (97)

    That fixed my problem. I learned alot from this exercise. Could you explain when you have to use chr(34) and when you have to use quotation marks?
    Thanks again for your help. John

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New records from old records (97)

    Strings are delimited by double quotes, so if you need to include a double quote in a string, you have a problem. This occurs frequently when you are assembling an SQL statement as a string in code, since you often need to include strings in an SQL statement, so you have a string within a string.

    I'll give a simplified example. Let's say that you want to set the value of the string variable strSomething to <font face="Georgia">LastName = "Jones"</font face=georgia>. The naive approach would be
    <pre>strSomething = "LastName = "Jones""</pre>

    But the interpreter would think that the string that begins with the first " ends with the next ", and the rest of the statement would cause an error since it is not valid VBA. There are several ways to get around this:
    1. <LI>Double the double quotes within the string:
      <pre>strSomething = "LastName = ""Jones"""</pre>

      <LI>Use single quotes to delimit the string within the string:
      <pre>strSomething = "LastName = 'Jones'"</pre>

      <LI>Use Chr(34); you must use this outside the quotes:
      <pre>strSomething = "LastName = " & Chr(34) & "Jones" & Chr(34)</pre>
    Using the first two methods, it can be difficult to distinguish all those quotes, therefore I mostly use method 3. But sometimes (depending on circumstances and mood), method 1 or 2 come in handy. In the end, it's a matter of taste. all three methods will work.

    Note: in the example, a name like O'Neil, containing a single quote, would cause problems with method 2.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •