not on list

[tankerman]

I have a form "frmEntry" based off of a table that has several relationships.
4 combo boxes based off of queries so that I can select a product, dock code
ect. if a product is not on the list in the drop down box I have a button on
the form to open up frmProductEntry so I can add a new product to my
tblProducts. Problem, how do I get my combo box to update without having to
close my frmEntry form.

I tried to add a refresh button but I have a VB on the TicketNo field for
when a duplicate TicketNo is entered and it won't work properly. Here is that
VB

Private Sub TicketNo_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Set db = CurrentDb
Dim z As Variant
Dim v As Variant
z = DLookup("[TicketNo]", "[tblEventsDetails]", "[TicketNo]= '" &
Me.[TicketNo].Value & "'")

If Not IsNull(z) Then
v = MsgBox("This Ticket Number has already been Entered, Would you like
to edit this record? ", vbYesNo, "Duplicate Number Entered")
If v = vbYes Then
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[TicketNo]= '" & z & " '"
If Not rs.NoMatch Then
Me.Undo
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
Else
Me.[TicketNo].Undo
MsgBox "Please enter a new Ticket Number"

End If
Cancel = True
End If

End Sub

Any help would be appreciated.

 

[Tom Wickerath]

Hi Tankerman,

... ect. if a product is not on the list in the drop down box I have a button on
the form to open up frmProductEntry so I can add a new product to my
tblProducts.

Rather than use a button, have you tried using the NotInList event procedure
for the combo box? You didn't state which version of Access that you are
using. If you happen to be using Access 2007, then you can achieve this goal
without VBA code.

Assuming you are using Access 2003 or lesser version, you can use VBA code
in the NotInList event procedure. I have an older sample here that you are
welcome to download:

http://www.seattleaccess.org/downloads.htm

See the following entry towards the bottom of the page:
"Not In List - Detailed Instructions by Tom Wickerath,
Download (16 kb)"

Also, Access MVP Allen Browne offers a web page with the same topic. His
covers the easier method for Access 2007, as well:

Adding values to lookup tables
http://allenbrowne.com/ser-27.html

Problem, how do I get my combo box to update without having to
close my frmEntry form.

Use the requery method: Me.NameOfComboBox.Requery

I tried to add a refresh button but I have a VB on the TicketNo field for
when a duplicate TicketNo is entered and it won't work properly.

"...it won't work properly" is not exactly enough information for us to
provide meaningful help. What does it do or not do? Have you set a break
point and stepped through the code one line at a time?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a form "frmEntry" based off of a table that has several relationships.
4 combo boxes based off of queries so that I can select a product, dock code
ect. if a product is not on the list in the drop down box I have a button on
the form to open up frmProductEntry so I can add a new product to my
tblProducts. Problem, how do I get my combo box to update without having to
close my frmEntry form.

I tried to add a refresh button but I have a VB on the TicketNo field for
when a duplicate TicketNo is entered and it won't work properly. Here is that
VB

Private Sub TicketNo_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Set db = CurrentDb
Dim z As Variant
Dim v As Variant
z = DLookup("[TicketNo]", "[tblEventsDetails]", "[TicketNo]= '" &
Me.[TicketNo].Value & "'")

If Not IsNull(z) Then
v = MsgBox("This Ticket Number has already been Entered, Would you like
to edit this record? ", vbYesNo, "Duplicate Number Entered")
If v = vbYes Then
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[TicketNo]= '" & z & " '"
If Not rs.NoMatch Then
Me.Undo
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
Else
Me.[TicketNo].Undo
MsgBox "Please enter a new Ticket Number"

End If
Cancel = True
End If

End Sub

Any help would be appreciated.

 

[tankerman]

Tom, (2003 is what I'm using) I used cbo TomsMethod you posted, I changed the
frm names, copied the Function IsLoaded2 to a module. I changed my Limit To
List on my cmbox to YES. I tried it out by putting in a barge name that I
knew was not in the list and here's what I got. When I tabbed to the next
field a box pop up telliing me that this barge name was not found, do you
wish to add this barge name? YES or NO. When I clicked YES my
frmBargeNamesEntry form pops up but just for a flash and then gone and the
message not on list pops up. Here is what I put in the On Not on List of my
combo box

Private Sub Combo19_NotInList(NewData As String, Response As Integer)
On Error GoTo ProcError
Dim strResponse As String

strResponse = MsgBox("Barge Name not found" & vbCrLf & "Do you wish to add
this Barge Name?", vbYesNo + vbInformation, "Please Respond")
If strResponse = vbYes Then
DoCmd.OpenForm "frmBargeNamesEntry", Datamode = acFormAdd, WindowMode =
acDialog, OpenArgs = NewData
If IsLoaded2("frmBargeNamesEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmBargeNamesEntry"
Else
Response = acDataErrContinue
End If
Else
Resonse = acDataErrContinue
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub

Hi Tankerman,

... ect. if a product is not on the list in the drop down box I have a button on
the form to open up frmProductEntry so I can add a new product to my
tblProducts.

Rather than use a button, have you tried using the NotInList event procedure
for the combo box? You didn't state which version of Access that you are
using. If you happen to be using Access 2007, then you can achieve this goal
without VBA code.

Assuming you are using Access 2003 or lesser version, you can use VBA code
in the NotInList event procedure. I have an older sample here that you are
welcome to download:

http://www.seattleaccess.org/downloads.htm

See the following entry towards the bottom of the page:
"Not In List - Detailed Instructions by Tom Wickerath,
Download (16 kb)"

Also, Access MVP Allen Browne offers a web page with the same topic. His
covers the easier method for Access 2007, as well:

Adding values to lookup tables
http://allenbrowne.com/ser-27.html

Problem, how do I get my combo box to update without having to
close my frmEntry form.

Use the requery method: Me.NameOfComboBox.Requery

I tried to add a refresh button but I have a VB on the TicketNo field for
when a duplicate TicketNo is entered and it won't work properly.

"...it won't work properly" is not exactly enough information for us to
provide meaningful help. What does it do or not do? Have you set a break
point and stepped through the code one line at a time?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a form "frmEntry" based off of a table that has several relationships.
4 combo boxes based off of queries so that I can select a product, dock code
ect. if a product is not on the list in the drop down box I have a button on
the form to open up frmProductEntry so I can add a new product to my
tblProducts. Problem, how do I get my combo box to update without having to
close my frmEntry form.

I tried to add a refresh button but I have a VB on the TicketNo field for
when a duplicate TicketNo is entered and it won't work properly. Here is that
VB

Private Sub TicketNo_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Set db = CurrentDb
Dim z As Variant
Dim v As Variant
z = DLookup("[TicketNo]", "[tblEventsDetails]", "[TicketNo]= '" &
Me.[TicketNo].Value & "'")

If Not IsNull(z) Then
v = MsgBox("This Ticket Number has already been Entered, Would you like
to edit this record? ", vbYesNo, "Duplicate Number Entered")
If v = vbYes Then
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[TicketNo]= '" & z & " '"
If Not rs.NoMatch Then
Me.Undo
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
Else
Me.[TicketNo].Undo
MsgBox "Please enter a new Ticket Number"

End If
Cancel = True
End If

End Sub

Any help would be appreciated.

 

[Tom Wickerath]

Hi Tankerman,

The only reason for the "2" added to the name of the IsLoaded function is
that this particular sample includes two similar, but not exactly the same,
functions for determining if a form is loaded. So, you can drop the "2"
everywhere, if you wish.

...do you wish to add this barge name? YES or NO.

Okay, so we've established that the NotInList is firing correctly.

When I clicked YES my frmBargeNamesEntry form pops up but just for a
flash and then gone

This sounds strange. Does this form include a module? If so, is there any
chance that you have a minimize, re-open as hidden, or close event firing? Do
you have any events for On Open, On Load, On Current, including perhaps a
macro called that might explain this behavior?

Dim strResponse As String

This should be declared as an integer: Dim intResponse As Integer
Hmmm....looks like my sample is incorrect (I just checked), since the MsgBox
function returns an integer. I should get that updated.

DoCmd.OpenForm "frmBargeNamesEntry", Datamode = acFormAdd,

WindowMode = acDialog, OpenArgs = NewData

You are missing a colon before the equals sign, when using named arguments.
This line of code should read as follows:

DoCmd.OpenForm "frmBargeNamesEntry", Datamode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData

I would think that without the colons, the code would not have compiled
correctly. Did you do a Debug > Compile | {ProjectName}
where {ProjectName} is the name of your VBA project? Also, do you have those
two most important words as the second line of code in your module: Option
Explicit?
If not, stop and read this "gem tip". Then configure your VBA editor so that
it will include Option Explicit for all new modules (you should add this to
any existing modules, as the second line of code, usually after Option
Compare Database:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Resonse = acDataErrContinue

Misspelled. Do a compile operation, after first adding Option Explicit to
all modules (or, as a minimum, to the modules for the two forms involved, but
eventually to all modules).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

Values are assigned to named arguments with the := operator not the =
operator, i.e.

DoCmd.OpenForm "frmBargeNamesEntry", Datamode:=acFormAdd,
WindowMode:=acDialog, OpenArgs:=NewData

What's happening in your case is that the form is opening, but not in
dialogue mode, so code execution is not being interrupted and is passing
almost immediately to the line:

DoCmd.Close acForm, "frmBargeNamesEntry"

which closes the form.

Here's a another example of this type of code (error handling omitted),
which differs slightly from yours in that before requerying the combo box it
checks to see that the new value has been added using the DLookup function:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

The Open event procedure of the frmCities form includes the following code:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Note that this sets the DefaultValue property of the City control on the
frmCities form, not its Value property. This means that a new record is not
initiated until the user adds the other data for the city, in this example
the County in which it is located. Consequently the user can back out of the
form without adding a new record if they wish.

Ken Sheridan
Stafford, England

Tom, (2003 is what I'm using) I used cbo TomsMethod you posted, I changed the
frm names, copied the Function IsLoaded2 to a module. I changed my Limit To
List on my cmbox to YES. I tried it out by putting in a barge name that I
knew was not in the list and here's what I got. When I tabbed to the next
field a box pop up telliing me that this barge name was not found, do you
wish to add this barge name? YES or NO. When I clicked YES my
frmBargeNamesEntry form pops up but just for a flash and then gone and the
message not on list pops up. Here is what I put in the On Not on List of my
combo box

Private Sub Combo19_NotInList(NewData As String, Response As Integer)
On Error GoTo ProcError
Dim strResponse As String

strResponse = MsgBox("Barge Name not found" & vbCrLf & "Do you wish to add
this Barge Name?", vbYesNo + vbInformation, "Please Respond")
If strResponse = vbYes Then
DoCmd.OpenForm "frmBargeNamesEntry", Datamode = acFormAdd, WindowMode =
acDialog, OpenArgs = NewData
If IsLoaded2("frmBargeNamesEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmBargeNamesEntry"
Else
Response = acDataErrContinue
End If
Else
Resonse = acDataErrContinue
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub

Hi Tankerman,

... ect. if a product is not on the list in the drop down box I have a button on
the form to open up frmProductEntry so I can add a new product to my
tblProducts.

Rather than use a button, have you tried using the NotInList event procedure
for the combo box? You didn't state which version of Access that you are
using. If you happen to be using Access 2007, then you can achieve this goal
without VBA code.

Assuming you are using Access 2003 or lesser version, you can use VBA code
in the NotInList event procedure. I have an older sample here that you are
welcome to download:

http://www.seattleaccess.org/downloads.htm

See the following entry towards the bottom of the page:
"Not In List - Detailed Instructions by Tom Wickerath,
Download (16 kb)"

Also, Access MVP Allen Browne offers a web page with the same topic. His
covers the easier method for Access 2007, as well:

Adding values to lookup tables
http://allenbrowne.com/ser-27.html

Problem, how do I get my combo box to update without having to
close my frmEntry form.

Use the requery method: Me.NameOfComboBox.Requery

I tried to add a refresh button but I have a VB on the TicketNo field for
when a duplicate TicketNo is entered and it won't work properly.

"...it won't work properly" is not exactly enough information for us to
provide meaningful help. What does it do or not do? Have you set a break
point and stepped through the code one line at a time?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a form "frmEntry" based off of a table that has several relationships.
4 combo boxes based off of queries so that I can select a product, dock code
ect. if a product is not on the list in the drop down box I have a button on
the form to open up frmProductEntry so I can add a new product to my
tblProducts. Problem, how do I get my combo box to update without having to
close my frmEntry form.

I tried to add a refresh button but I have a VB on the TicketNo field for
when a duplicate TicketNo is entered and it won't work properly. Here is that
VB

Private Sub TicketNo_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Set db = CurrentDb
Dim z As Variant
Dim v As Variant
z = DLookup("[TicketNo]", "[tblEventsDetails]", "[TicketNo]= '" &
Me.[TicketNo].Value & "'")

If Not IsNull(z) Then
v = MsgBox("This Ticket Number has already been Entered, Would you like
to edit this record? ", vbYesNo, "Duplicate Number Entered")
If v = vbYes Then
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[TicketNo]= '" & z & " '"
If Not rs.NoMatch Then
Me.Undo
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
Else
Me.[TicketNo].Undo
MsgBox "Please enter a new Ticket Number"

End If
Cancel = True
End If

End Sub

Any help would be appreciated.

 

[tankerman]

Tom, I made the changes that you suggested, I put in the : before my = where
you indicated, corrected my spelling, followed the "gem tip". Now, it works
almost, NotInList is working, when YES is selected my frmBargeNamesEntry
opens, I fill out the fields and close the form, everything seems to be
working until I tab to the the next field then NotInList fires again. If i
fill out the form with the same barge name i get the dupicate error message.
I think my BargeName control is not requerying so that the new name is added
to the box.

 

[Tom Wickerath]

Hi Tankerman,

I fill out the fields and close the form, .....

Are you closing the form, or hiding the form (ie. re-opening it in hidden
mode)?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

___________________________________


Tom, I made the changes that you suggested, I put in the : before my = where
you indicated, corrected my spelling, followed the "gem tip". Now, it works
almost, NotInList is working, when YES is selected my frmBargeNamesEntry
opens, I fill out the fields and close the form, everything seems to be
working until I tab to the the next field then NotInList fires again. If i
fill out the form with the same barge name i get the dupicate error message.
I think my BargeName control is not requerying so that the new name is added
to the box.

 

[tankerman]

Tom, after I fill the 4 fields out I close the form frmBargeNameEntry, field
list for this form is taken from tblBargeNames. My frmEventEntry BargeName
control source is BargeName, Row Source tblBargeNames.

I tried after entering a new barge name and fill out the barge name entry
from, closing the form, remove the new name from frmevententry bargename
field and putting in one that was already their, finish fill the form and
going to a new record, the barge name was still not their but if i close
frmevententry form and reopen the new name is available. I don't want to have
to exit the form to get the new barge name to be available.

After I close my frmbargenameentry form I checked to see if my tblbargenames
table had the new name and it did but my frmEventEntry form still did not
have the BargeName until I closed the form and reopened it.

 

[tankerman]

I think I figure out how to at least how to get the form to requery only the
single field. I had been putting "me.BargeName.requery" and this was not
working, combo 19 not the name is the proper way me.combo 19.requery in the
on click of the button I put on the form works. could I put this in my
NotInList Sub somewhere so that i don't have to use a button. I have 4 combo
boxes so I would have 4 buttons to requery each field individual field but
only if it needs to be requeryed.

 

[Tom Wickerath]

Hi Tankerman,

You shouldn't need the four command buttons that you have indicated. In your
previous reply, you wrote:

"I don't want to have to exit the form to get
the new barge name to be available."

You shouldn't need to exit the form to get this data available. Tell you
what.....is it possible for you to send me a copy of your database, with any
sensitive data removed? If so, I will be happy to take a look at it for you.
Please compact the database first (Tools > Database Utilities > Compact and
repair database), and then add it to a .zip file, if you have this type of
file compression software available (WinZip, for example).

If you are interested, send me a private e-mail message with a valid
reply-to address. Use the same subject as this thread. My e-mail address is
available at the bottom of the contributor's page indicated below. Please do
not post your e-mail address (or mine) to a newsgroup reply. Doing so will
only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________