Adding records to a table through a form

[FoxProProgrammer]

I am creating a form that will be used to add new records to a database. The table contains four fields, one of which is a foreign key to its parent table. I use DoCmd.RunCommand acCmdSaveRecord to save the record when the user presses the “Save” button on the form. I don’t want to display the foreign key on the form because it isn’t needed. The foreign key is the selected value of a combo box on another form. The only way that I have gotten this to work is to create a hidden text box on the form, whose value is assigned with [Forms]![form name]![control name].

Is this how this is typically done or is there a better way? It seems rather convoluted to create a control on the form that is hidden just so the data is saved with the record. Is there a way to assign the value of a field in a table? If there’s a way to do that, I could save the three fields with the DoCmd and then save the foreign key after that. This seems dangerous because if the record pointer changes between the time the first three fields are saved and the fourth field, the data would get corrupted. I realize that there is more than one way to code, but I am trying to do this in a manner that is most consistent with good programming practices in Access, if that even exists.

Thanks for any suggestions.

dz

 

[Isadore]

Fox --

On the Save button you could use:

Dim db As Database, rst As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
'determine if the hidden form is open..
If IsFormLoaded("frmHiddenForm&quot Then
'proceed to save record...
Set rst = db.OpenRecordset("tblMyTable&quot
rst.AddNew
rst![ID] = Forms![frmHiddenForm]![ID]
rst![FieldA] = Me![FieldA]
rst![FieldB] = Me![FieldB]
rst![FieldC] = Me![FieldC]
rst.Update
rst.Close
db.Close
Exit Sub
Else
Exit Sub
End If

Use the following Function in a public module:

Public Function IsFormLoaded(frmName As String) As Boolean
Dim i As Integer
For i = 0 To Forms.Count - 1
If (Forms(i).FormName = frmName) Then
IsFormLoaded = True
Exit Function
End If
Next i
End Function

In this way Fox you will never save the record unless the hidden form is "opened"...you could also put an IF..THEN statement right after the IsLoaded IF..THEN to verify that the foreign value is there, is not a null, is adequate, etc... Also, if the attempt fails, i.e., the Hidden form is not open, or the value of it's ID is inadequate, etc... you might want to close this form using a DoCmd.Closeform statement and reject the user's attempt at that time, etc. etc...

 

[experienced]

Dear Fox,

Isadore's suggestion should work. I would add a few things. First, if you are new to Access, do learn ADO, not DAO. DAO is a dying technology. Write your codes in ADO. Another point is that if you want to make sure the foreign key is present, you want to use the OpenArgs property of the form to send data to the Add form. So, let's first modify the DoCmd line in your main form:

DoCmd.OpenForm "frmAddCal",,,,,acDialog, Nz(Me!ID)

The last item is for OpenArgs: you can send a string to the form you are about to open, so you can send the foreign key value this way. (Read Help)

Now on the frmAddCal, you should declare a variable, ID, or create a control,ID, and make it invisible.

Then, create a module:

Private Sub Form_Open()

If Nz(Me.OpenArgs) <> &quot;&quot; Then
ID = Me.OpenArgs

' If ID is a number, use the following line instead:
' ID = Val(Me.OpenArgs)

Else
Beep
MsgBox &quot;Cannot find the ID.&quot;
Docmd.Close
End If

End Sub

This will read the string sent from the calling form and assign it to the appropriate control (or variable).

Next, upon pressing Save Button, you can create a module like:

Private Sub cmdSave_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

On Error Goto Err_cmdSave_Click

' You may want to check if all the required fields
' are entered before you save it.

If Nz(FieldA) = &quot;&quot; _
Or Nz(FieldB) = &quot;&quot; _
Or Nz(FieldC) = &quot;&quot; Then
Beep
Msgbox &quot;You must fill all the fields.....&quot;
Exit Sub
End If

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.open &quot;tblMyTable&quot;, cnn, adOpenStatic, adLockOptimistic
With rs
.AddNew
!FieldA = Me!FieldA
.....
.Update
.Close
End With
Set rs = Nothing
Set cnn = Nothing

MsgBox &quot;Record saved.&quot;

Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub

Now in the above sample, you may want to add more lines, for example, to check if the record already exists, etc. If you are not familiar with ADO, get a book about ADO or an Access book that has a long chapter about ADO.

Cheers

 

[Isadore]

Experienced - thanks for the code -- an excellent summary...will take a closer look at ADO...

 

[Isadore]

Experienced -- small excerpt from Access 2000 -- and the change is very clear...excellent point for all Access programmers...especially for new developments...

********

&quot;...Access 2000 represents the pivot point of Microsoft's strategy for data access. Under the umbrella of &quot;Universal Data Access,&quot; Microsoft wants all Office users, not just Access developers, to abandon Data Access Objects (DAO), ODBCDirect, and the Open Database Connectivity (ODBC) Application Programming Interface (API). Microsoft proposes to substitute a completely new approach to data access based on a new collectin of Component Object Model (COM)interfaces called OLE DB.... From 1999 on, OLE DB and its derivatives, ActiveX Data Objects (ADO) and ActiveX Data Object Extensions (ADOX), are Microsoft's mainstreamdata access technologies.&quot;

When you upgrade an existing application from an earlier Access version to Access 2000, DAO 3.6 is used, which is backward compatible... ...all new applications you create in Access 2000 use ADO 2.1, ADOX 2.1, and OLE DB 2.0. New and upgraded applications that use Access (Jet) databases continue to use Jet 4.0 Access Data Projects (ADP) use ADO 2.1 and OLE DB to connect directly to the Microsoft DAta Engine (MSDE) and SQL Server 6.5+, but don't use Jet.

-from Access 2000, QUE

Experienced - is this a fair comparison of the newer use of ADOB v. DAO?

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open &quot;tblMyTable&quot;, cnn, adOpenStatic, adLockOptimistic
With rs
.AddNew
!FieldA = Me!Field A
......
.Update
.Close
End With
Set rs = Nothing
Set cnn = Nothing

v.

Set dbs = DBEngine.Workspaces(0).Databases(0)
Set rst = dbs.OpenRecordset(&quot;tblMyTable&quot
rst.AddNew
rst![FieldA] = Me!FieldA
......
rst.Update
rst.Close
dbs.Close

 

[FoxProProgrammer]

Isadore and Experienced:

Thanks so much for your gracious help. I realize how much time it took for you to write your reply, and I am very grateful. I printed out your code and am studying it now. Thanks also for your suggestion to learn ADO instead of DAO. I'll heed that advice...but can I go back to FoxPro now? lol The two environments are so different that I imagine few Access programmers like FoxPro and few FoxPro programmers like Access...that is until you get used to the other one and then you probably don't want to go back. Thanks again for all your help.

dz

 

[Taha60]

Hello....

I wanted to find out if writing/using SQL insert statements would be easier.. For instance ...

Insert Into jobs Values ('worker', 50, 100) where the table jobs has 3 fields ... type of job ... hours ... pay per hour....

I needed to do exactly what is being described here ... and I am thinking of doing it this way.... Any suggestions any one?

 

[cheerio]

You can use SQL.

The one caution I have is that Access SQL is a dialect that is not standard and is not the same as other MS products such as MS SQL Server. For example to search for me in Access you might have a SQL where clause of

where forename like "Ken*"

For MS SQL Server

where forename like 'Ken%'

If the project is later migrated say to MS SQL Server the Access specific SQL code may not work.

Both DAO and ADO code are more robust although you would still need to repoint your connections to the server based tables.

If you are comfortable with SQL go for it knowing the limitations.

Ken