How to make button generate records

[zkarj]

While there are lots of detailed issues discussed and resolved here, my problem is at a much higher level. Here's what I want to do.

I want to generate records in a file based on the user entering some 9 different values. The first two values will be used to get records from file A (they are a partial key). The values from the records in file A, plus the other values entered by the user will be used to generate records in file B. I hope this is clear enough.

I have created a query which prompts the user for each field and does the job just fine, but I want to replace the 9 individual prompts with a single form which allows the use of combo boxes for the partial key and a date picker for the single date field.

I've fluffed through creating a form with unbound controls to suit and even worked out a form of cascading combo box for the partial key, but I am struggling to find the best way to make a button at the bottom generate those records.

A nice optional would be to then display the added records (i.e. another query I guess).

Can somebody give me some pointers as to how to approach this? I've done very little in the way of Access programming, being mostly an SQL guy.

Thanks.

 

[RuralGuy]

Something like this should do it:

Private Sub cmdAddNewRecord()
On Error GoTo Err_AddNewRecord
Dim MyRs As DAO.Recordset
'-- Add a new Record to the "tblRecords" Table
Set MyRs = CurrentDb().OpenRecordset("tblRecords", dbOpenDynaset)
With MyRs
   .AddNew
   ![Field1] = Me.txtField1
   ![Field2] = Me.txtField2
   ![Field3] = Me.txtField3
   ![Field4] = Me.txtField4
   ![Field5] = Me.txtField5
   .Update
End With

Exit_AddNewRecord:
    On Error Resume Next
    MyRs.Close
    Set MyRs = Nothing
    Exit Function

Err_AddNewRecord:
   MsgBox "Error No:    " & Err.Number & vbCr & _
              "Description: " & Err.Description
   Resume Exit_AddNewRecord
End Sub

 

[zkarj]

Thanks RG. However, I think I have not explained my requirement clearly enough. I am a programmer, just not a VB programmer, so I think I understand what your code does.

It appears to use only a single file to source and add records. In my case, I want to source records from one table and add to another. Perhaps some actual detail will help. This is a cut down version of what I have:

tblApplicationUse (tells me which apps a project is using in a specific phase)
Project
Phase
Application

tblRelease (tells me which releases of code are due when and for what apps)
ReleaseID
Project
Phase
Application
Version
InstallDate

I want the user to be able to enter
Project
Phase
InstallDate

And from that generate a new tblRelease record for each record in tblApplicationUse. I.e. the Project, Phase select records from tblApplication Use. From that record set, Project, Phase, Application are used in conjunction with the entered InstallDate to generate new tblRelease records. (The ID is an Autonumber, and the Version should remain blank).

The user will then go and modify and/or remove any records as necessary. It could end up with some apps being installed on different days, or not at all, but this gives them a good start.

 

[zkarj]

Further detail

Perhaps to illustrate most clearly, here is the actual query I wrote which does the job in the ugly fashion.

INSERT INTO tblRelease (Project, Phase, Application, Version, Date,
                        DateVariance, Time, Reference, Description,
                        Impact, Comment)

SELECT [Enter Project], [Enter Phase],
       tblApplicationInstance.Application, "", DateValue([Enter Date]),
       [Enter Date Variance], [Enter Time],
       [Enter Reference], [Enter Description],
       [Enter Impact], [Enter Comment]

FROM tblApplicationUse INNER JOIN tblApplicationInstance
  ON tblApplicationUse.[Application Instance ID]=tblApplicationInstance.ID

WHERE tblApplicationUse.Project=[Enter Project] And
      tblApplicationUse.Phase=[Enter Phase];

This differs from my cut-down version not only in extra fields, but also by having to go to an extra table to get the actual Application name.