Not In List

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

I have to believe many of you have implemented something
similar to this situation before. I'd be grateful for
helping me implement this, as it's the *last* issue before
releasing this app to our user group.

In response to the NotInList event, I'd like to show the
user a data entry table, and provide this functionality:

- Copy the newly entered value to the new record
- Present Save and Cancel buttons:
- Save closes the form, returns to the original form,
and moves to the next control
- Cancel deletes the record, and returns to the original
form

TIA
Kevin Sprinkel
 
On my website (see sig below) are two small sample databases called
"NotInList.mdb" and "NotInListAlternatives.mdb" which illustrate how to do
this.
 
-----Original Message-----
On my website (see sig below) are two small sample databases called
"NotInList.mdb" and "NotInListAlternatives.mdb" which illustrate how to do
this.
Click to expand...

Thanks, Roger. Option 5 in NotInList is precisely what
I'm looking for, however, after modifying your code, I'm
getting error 3137, Missing semicolon (;) at end of SQL
statement:

Private Sub cboJobNumber_NotInList(NewData As String,
Response As Integer)
Dim strsql As String, x As Integer
Dim strFrmName As String
Dim LinkCriteria As String

x = MsgBox("Do you want to add this project?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tblProjects values ([JobNumber])
('" & NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[JobNumber] = '" & Me!cboJobNumber
& "'"
DoCmd.OpenForm strFrmName, , , LinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Best regards.
Kevin Sprinkel
 
Was just flicking through the msgboard and noticed a syntax error in your
code which is probs causing your error...

Your line:

strsql = "Insert Into tblProjects values ([JobNumber]) ('" & NewData &
"')"

Should read:

strsql = "Insert Into tblProjects ([JobNumber]) values ('" & NewData &
"')"

'values' was in the wrong place

Hope this sorts it!
Kel


Kevin Sprinkel said:
-----Original Message-----
On my website (see sig below) are two small sample databases called
"NotInList.mdb" and "NotInListAlternatives.mdb" which illustrate how to do
this.
Click to expand...

Thanks, Roger. Option 5 in NotInList is precisely what
I'm looking for, however, after modifying your code, I'm
getting error 3137, Missing semicolon (;) at end of SQL
statement:

Private Sub cboJobNumber_NotInList(NewData As String,
Response As Integer)
Dim strsql As String, x As Integer
Dim strFrmName As String
Dim LinkCriteria As String

x = MsgBox("Do you want to add this project?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tblProjects values ([JobNumber])
('" & NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[JobNumber] = '" & Me!cboJobNumber
& "'"
DoCmd.OpenForm strFrmName, , , LinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Best regards.
Kevin Sprinkel
Click to expand...
 
Your line:
strsql = "Insert Into tblProjects values ([JobNumber]) ('" & NewData &
"')"

Should read:

strsql = "Insert Into tblProjects ([JobNumber]) values ('" & NewData &
"')"
Click to expand...

Thanks, Kel. This has solved *this* problem--the form
opens, however, the link criteria must not be working.
The form displays a blank record. After adding the other
fields (other than the key field in the LinkCriteria), I
get the message that the key field cannot be Null. On
exiting and examining the table, the record that was
inserted is there with only its key field.

Any idea what's wrong with the LinkCriteria? It looks
right to me.

Thank you.
Kevin Sprinkel
 
Seems to me that your sql statement may be failing and the new value is
never actually getting inserted into your table in the first place -
therefore your filter is just bringing up a new record because it can't find
the one you are looking for.

If you have any required fields, etc in your tblProjects table, then the new
value won't be inserted into the table as it will also need values for the
required fields.

I couldn't really say anymore without knowing further details of your
database design, but it seems that this may be the case. If it is, you will
probably have to actually open the data-entry form without inserting the
value first but pass the new value as an argument to the data-entry form.
Then use the form's Load event to set the JobNumber field to the argument
passed to the form (using openargs).

After you have entered the data for the record, it can all be saved using
the Save button, then using the After Insert event, set the cboJobNumber to
the JobNumber value and then refresh the cboJobNumber combobox.

Hope this helps, but if the above isn't clear and you want to try and need
code examples, let me know....

Otherwise have fun ;)

Kel


Kevin Sprinkel said:
Your line:

strsql = "Insert Into tblProjects values ([JobNumber]) ('" & NewData &
"')"

Should read:

strsql = "Insert Into tblProjects ([JobNumber]) values ('" & NewData &
"')"
Click to expand...

Thanks, Kel. This has solved *this* problem--the form
opens, however, the link criteria must not be working.
The form displays a blank record. After adding the other
fields (other than the key field in the LinkCriteria), I
get the message that the key field cannot be Null. On
exiting and examining the table, the record that was
inserted is there with only its key field.

Any idea what's wrong with the LinkCriteria? It looks
right to me.

Thank you.
Kevin Sprinkel
Click to expand...
 
-----Original Message-----
Seems to me that your sql statement may be failing and the new value is
never actually getting inserted into your table in the first place -
therefore your filter is just bringing up a new record because it can't find
the one you are looking for.

If you have any required fields, etc in your tblProjects table, then the new
value won't be inserted into the table as it will also need values for the
required fields.
Click to expand...

Thanks, Kel, for hanging in there, and for all your ideas.
Actually, it turned out I'd never toggled the DataEntry
property back to No after switching NotInList stategies.
The Insert had always worked, but DataEntry=Yes forced it
to open to a new blank record.

Now, that is working--the form opens to the correct
record, and saves the entire new record. It does not,
however, show the new data (other than the key which was
entered in the combo box) on returning to the original
form. I tried the Refresh and Requery methods for both
the controls and the form; none worked. Do you know why?

The main form's control names and Control Source
properties are:

cboJobNumber SELECT tblProjects.JobNumber,
tblProjects.Description FROM tblProjects;

txtDescription =cboJobNumber.Column(1)


The NotInList subroutine is:

Private Sub cboJobNumber_NotInList(NewData As String,
Response As Integer)
Dim strsql As String, x As Integer
Dim strFrmName As String
Dim strLinkCriteria As String

strFrmName = "frmProjects"
x = MsgBox("Do you want to add this project?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tblProjects ([JobNumber]) values
('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
strLinkCriteria = "[JobNumber] = '" & NewData & "' "
DoCmd.OpenForm strFrmName, , , strLinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

I had thought that the line, "Response = acDataErrAdded"
would ensure that the data was there on return.

TIA
Kevin Sprinkel
 
Hi Kevin - Glad you got your other problem sorted - aren't those fiddly
little errors annoying?

I don't really understand what your new problem is - is it that none of the
new data is appearing in the combobox list, or that the new data is in the
list but not automatically on the new value, or is the new jobnumber
appearing in the list but no the description to go with it? Or none of the
above??

Kel


Kevin Sprinkel said:
-----Original Message-----
Seems to me that your sql statement may be failing and the new value is
never actually getting inserted into your table in the first place -
therefore your filter is just bringing up a new record because it can't find
the one you are looking for.

If you have any required fields, etc in your tblProjects table, then the new
value won't be inserted into the table as it will also need values for the
required fields.
Click to expand...

Thanks, Kel, for hanging in there, and for all your ideas.
Actually, it turned out I'd never toggled the DataEntry
property back to No after switching NotInList stategies.
The Insert had always worked, but DataEntry=Yes forced it
to open to a new blank record.

Now, that is working--the form opens to the correct
record, and saves the entire new record. It does not,
however, show the new data (other than the key which was
entered in the combo box) on returning to the original
form. I tried the Refresh and Requery methods for both
the controls and the form; none worked. Do you know why?

The main form's control names and Control Source
properties are:

cboJobNumber SELECT tblProjects.JobNumber,
tblProjects.Description FROM tblProjects;

txtDescription =cboJobNumber.Column(1)


The NotInList subroutine is:

Private Sub cboJobNumber_NotInList(NewData As String,
Response As Integer)
Dim strsql As String, x As Integer
Dim strFrmName As String
Dim strLinkCriteria As String

strFrmName = "frmProjects"
x = MsgBox("Do you want to add this project?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tblProjects ([JobNumber]) values
('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
strLinkCriteria = "[JobNumber] = '" & NewData & "' "
DoCmd.OpenForm strFrmName, , , strLinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

I had thought that the line, "Response = acDataErrAdded"
would ensure that the data was there on return.

TIA
Kevin Sprinkel
Click to expand...
 
I don't really understand what your new problem is - is
it that none of the
new data is appearing in the combobox list, or that the new data is in the
list but not automatically on the new value, or is the new jobnumber
appearing in the list but no the description to go with it? Or none of the
above??

Kel
Click to expand...

When I return to the original form, the value remains in
the combo box just as it was typed. The cursor is
positioned in the next enabled control, and no Description
displays.

If I go back to the combo box, the new values are there,
and if I then select it, the Description displays normally.

Thanks for your help.

Kevin Sprinkel
 
Am I right in thinking that it is just the fact that the description doesn't
show immediately in txtDescription?

If this is the case you do just have to requery the combobox cboJobNumber
after you have saved the new record in the dialog box - this should then
display your description - everything else seems to work fine (I gave your
code a try :) )

Hope this helps
Kel
 
Am I right in thinking that it is just the fact that the
description doesn't show immediately in txtDescription?

If this is the case you do just have to requery the
combobox cboJobNumber after you have saved the new record
in the dialog box - this should then display your
description - everything else seems to work fine (I gave
your code a try :) )
Click to expand...
Hope this helps
Kel
Click to expand...

Kel, you are a gem for hanging in with me on this. I had
thought to requery the combo box, but it was a matter of
where--I tried the original form's On Current, On Got
Focus events. Nothing happened, and, in fact these events
were not triggered, as if focus had not passed to the
dialog form and back.

Putting it in the OnNotInList event triggered an error
saying the value needed to be saved first. I placed
MsgBox statements in the code and learned that in the
NotInList event, the combo box value was NOT the NewData
value, and in fact was Null if it was the first attempt.
Once back in the original form, however, the combo box had
the correct value, so I requeried it from the OnGotFocus
event of the next control, which was successful.

I'm going back to my references to further refine my
understanding of event triggering. I can now put this
application on the network for all of us, and no doubt,
began a *new* learning curve. ;^)

I couldn't have done it without you; thanks again.

Best regards.
Kevin Sprinkel
 
Hi Kevin

So glad to have been of help!!

Can I make one last suggestion though? - Running it from the OnGotFocus
event of the next control seems a little bit prone to unforseeable problems
as it depends on the control getting the focus - Can I suggest a more
reliable place to run the requery from would be the AfterUpdate event of the
dialog form - this will run after the record has been saved and before the
form has closed.

This is just a suggestion - feel free to ignore!!!

Otherwise good luck!!
Kel
 
Back
Top