Make Field Enterable

[Andrew]

My form is based on a OUTER JOIN query. The user wants a note field
(actually containing the client’s vendor preferences) based on the client_ID
which is chosen from a combo box. The client_ID is taken from a linked table
in a SQL Server database that I’m not allowed to touch. So, I created
another table (CLIENT_SUPPLEMENT) with two columns: Client_Id (Primary Key),
Vendor_Information, and added the table to the OUTER JOIN.

On the form, the Vendor_Information control is editable if there is a
client_ID in the CLIENT_SUPPLEMENT table that matches the client_ID (from the
aforementioned linked table) displayed in the form. However, it is,
logically, not enterable if there no row exists for a client in
CLIENT_SUPPLEMENT.

I grab the client_ID from the form record, INSERT it into the
CLIENT_SUPPLEMENT table, requery. I do this on the ON ENTER event of the
vendor_information control. The INSERT works, but for some reason I have to
close the form and reopen it before I can edit vendor_information. How can I
make that field enterable?

Private Sub Vendor_Information_Enter()

Dim strSQL As String

On Error GoTo Err_Vendor_Information_Enter

strSQL = "INSERT INTO client_supplement (client_ID) " & _
"VALUES (""" & Me![client_ID] & """);"

DoCmd.RunSQL strSQL
Me.Requery


Err_Vendor_Information_Enter:
‘ code to handle primary key violation
Exit Sub

End Sub

 

[Andrew]

I rewrote the whole event from scratch and now it works. If anyone's
interested, here is the code. Maybe it can be improved upon.

Private Sub Vendor_Information_Enter()

'************************************************************' Because of
the outer join, this field does not accept data. However,
' it is updateable if there is already a record in the CLIENT_SUPPLEMENT
' table.
'
' First check to see if a row exists in CLIENT_SUPPLEMENT. If it does
' then there's nothing to do. This field is enterable. If not,
' INSERT a row into CLIENT_SUPPLEMENT.
'
' Requery and navigate back to the record the user was using.
'
'************************************************************Dim
strSQLinsert As String
Dim strSQLselect As String
Dim sIsThere As String
Dim sRecordKey As String
Dim db As Database
Dim rs As Object

DoCmd.SetWarnings (False)

strSQLselect = "SELECT min(client_id) FROM client_supplement " & _
"WHERE client_ID = """ & Me![client_ID] & """;"

strSQLinsert = "INSERT INTO client_supplement (client_ID) " & _
"VALUES (""" & Me![client_ID] & """);"

' MsgBox ("client: " & Me![client_ID])

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQLselect)
sIsThere = Nz(rs.Fields(0), "None")
rs.Close
' MsgBox ("result: " & sIsThere)

If sIsThere = "None" Then
DoCmd.RunSQL (strSQLinsert)
If Me.Dirty Then 'Save the record before continuing the requery
Me.Dirty = False ' Access will save the record when you force this
property to false
End If
sRecordKey = Me.requestID
Me.Requery ' Requeries, but goes to the first record
' But we've saved the requestID so we can find our way back
' The following puts all the records in memory so you can navigate
With Me.RecordsetClone
.FindFirst "RequestID = " & sRecordKey
If .NoMatch Then
MsgBox ("Your record disappeared")
Else
' Use bookmarks to synchronize the form with
' the recordsetclone record you found using .FindFirst
Me.Bookmark = .Bookmark
End If
End With
End If
' Return to the control where you started this whole thing

Me.Vendor_Information.SetFocus

DoCmd.SetWarnings (True) ' Don't know if this is necessasry

End Sub

My form is based on a OUTER JOIN query. The user wants a note field
(actually containing the client’s vendor preferences) based on the client_ID
which is chosen from a combo box. The client_ID is taken from a linked table
in a SQL Server database that I’m not allowed to touch. So, I created
another table (CLIENT_SUPPLEMENT) with two columns: Client_Id (Primary Key),
Vendor_Information, and added the table to the OUTER JOIN.

On the form, the Vendor_Information control is editable if there is a
client_ID in the CLIENT_SUPPLEMENT table that matches the client_ID (from the
aforementioned linked table) displayed in the form. However, it is,
logically, not enterable if there no row exists for a client in
CLIENT_SUPPLEMENT.

I grab the client_ID from the form record, INSERT it into the
CLIENT_SUPPLEMENT table, requery. I do this on the ON ENTER event of the
vendor_information control. The INSERT works, but for some reason I have to
close the form and reopen it before I can edit vendor_information. How can I
make that field enterable?

Private Sub Vendor_Information_Enter()

Dim strSQL As String

On Error GoTo Err_Vendor_Information_Enter

strSQL = "INSERT INTO client_supplement (client_ID) " & _
"VALUES (""" & Me![client_ID] & """);"

DoCmd.RunSQL strSQL
Me.Requery


Err_Vendor_Information_Enter:
‘ code to handle primary key violation
Exit Sub

End Sub