Not In List Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the below code in the Not in List function of my database

Private Sub Combo17_NotInList(NewData As String, Response As Integer
Dim strsql As String, x As Intege
Dim LinkCriteria As Strin
x = MsgBox("Do you want to add this value to the list?", vbYesNo
If x = vbYes The
strsql = "Insert Into T_Names ([Stud_Name]) values ('" & NewData & "')
'MsgBox strsq
CurrentDb.Execute strsql, dbFailOnErro
LinkCriteria = "[Stud_Name] = '" & Me!Combo17.Text & "'
DoCmd.OpenForm "F_MaintainNames", , , LinkCriteri

Response = acDataErrAdde
Els
Response = acDataErrContinu
End I
End Su

I have the tables T_Names (fields: Stud_Name and Stud_ID) and T_Car (fields: Stud_ID, Make, Model etc). My main form is basically used as a search function for the names table with a combo box for selecting the record by the persons name, and also has a textbox that is updated with a corresponding Stud_ID value when the name is selected. THe subform, which is used to update data into the Car table, automatically moves to the corresponding record when it is selected, also showing the same Stud_ID that is shown in the previously mentioned text box in a different text box

Now, when i use the MaintainNames form to update the names and stud id when they are not found in the list, the data gets added to the T_Names table, but it is not reflected on the form anywhere unless i close the form and reopen it. I have tried various requery functions but still cannot get it to work. What i want to happen is when i exit the MaintainNames form, the name should be in the combo box and when selected it should function as per normal, ie. the Stud-ID text boxes should be updated and ready for data entry! Can anyone help me!!!
 
Luke,

If I understand you correctly, you fire up the MaintainNames dialog from
another form, and you want (presumably) a combo box on the original form to
include the new name added while on the MaintainNames dialog. Is this
correct?

If so, you have two options:

1. Use the fully-qualified name of the original form's combo box to requery
it:
Forms!frmMyForm!cboMyCombo.Requery

....but that contravenes the rule of reuse. Once you go down that road, you
must add specific code to handle every other form/object that uses that
dialog.

In my opinion, you're better to leave all specific form/object-related code
in the form/object that calls the dialog. In that way, the dialog is truly
reusable and independent. The following link shows you how to do that:
http://www.pacificdb.com.au/MVP/Code/MyDialog.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Luke said:
I have the below code in the Not in List function of my database:

Private Sub Combo17_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
Dim LinkCriteria As String
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into T_Names ([Stud_Name]) values ('" & NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[Stud_Name] = '" & Me!Combo17.Text & "'"
DoCmd.OpenForm "F_MaintainNames", , , LinkCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

I have the tables T_Names (fields: Stud_Name and Stud_ID) and T_Car
Click to expand...
(fields: Stud_ID, Make, Model etc). My main form is basically used as a
search function for the names table with a combo box for selecting the
record by the persons name, and also has a textbox that is updated with a
corresponding Stud_ID value when the name is selected. THe subform, which
is used to update data into the Car table, automatically moves to the
corresponding record when it is selected, also showing the same Stud_ID that
is shown in the previously mentioned text box in a different text box.
Now, when i use the MaintainNames form to update the names and stud id
Click to expand...
when they are not found in the list, the data gets added to the T_Names
table, but it is not reflected on the form anywhere unless i close the form
and reopen it. I have tried various requery functions but still cannot get
it to work. What i want to happen is when i exit the MaintainNames form,
the name should be in the combo box and when selected it should function as
per normal, ie. the Stud-ID text boxes should be updated and ready for data
entry! Can anyone help me!!!
 
Back
Top