Unbound Combo - other fields don't update 2

[TimTang]

Hi all Access experts and Gurus!

I have a form called frmPosition which tracks the position of ships. It retrieves data from a Query called tblPositionsQuery. My form is made up of many bound combo boxes as well as other assorted fields. The key field is cboVesselName which is unbound. I'm doing this because I want to be able to select existing record by typing in a portion of the vessel name to be able to select the record for editing. I also have it set up so if a ship isn't in the list then you have an option to add the new ship to the table (i found code on the internet to do this). It works fine; it adds the new record to the table with all remaining fields blank exactly as it should.

The problem is that the other fields in the form aren't aware that the new record exists, so all fields are filled with the first record in the table even though the ships name has changed. If I use the mouse roller fields change as though your scrolling through the vessel list, except the vessel name remains as the new vessel just added.

If I close and reopen the form then go to the newly added vessel, the feilds are empty just as they should be ready for data entry.

How can I do this with out closing and reopening the form?

Any assistance would be greatly appreciated.

 

[Remou]

Have you tried adding:

Me.Requery

To the event that adds the new ship?

 

[TimTang]

Thanks for the quick replay Remou

I tried adding Me.Requery but then the message box "Do you want to add it?" goes into an infinite loop and I have to Ctrl-Alt-Del to shut down Access.

I have two events on this unbound combo box the code is:

Private Sub cboVesselName_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RetrievalField] = '" & Me![cboVesselName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

And:

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

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Vessel...")
If i = vbYes Then
strSQL = "Insert Into tblPositionsQuery ([VESSEL NAME]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Me.Requery

End Sub

The first Sub populates the other fields based on the vessel name.

The second adds a new vessel record to the table. I added Me.Requery at the bottom of the second one.

Any Ideas?

 

[Remou]

I think we may be going about this the wrong way. The combo is used to find vessels on a form and it seems that you wish to create a new vessel if the name is not already listed. It would be best to add the new vessel and then requery the combo, rather than using the not in list event to add the new record.

You can use the After Update event for eveything:

Private Sub cboVesselName_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[RetrievalField] = '" & Me![cboVesselName] & "'" 
    If Not rs.EOF Then 
       Me.Bookmark = rs.Bookmark
    Else
       If MsgBox ("Do you want to create a new vessel?", _
          vbYesNo)=vbYes Then
          DoCmd.GoToRecord , , acNewRec
       Else
          'What to do ...
       End If
    End If
    
End Sub

You can then requery the combo in the AfterInsert event.

 

[TimTang]

Hi Remou

I tried your code in _AfterUpdate event but I end up with this message box.


"The text you entered isn't an Item in the list"

"select an item from the list or enter text that matches one of the listed items"

I can't get beyond this point and the new ship is not added to the table

Thanks for the idea any way.

It's hard to believe something so simple would be this hard to crack.

 

[Remou]

You will need to change Limit to List on the combo to No. I also notice a mistake in the code above:

Private Sub cboVesselName_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[RetrievalField] = '" & Me![cboVesselName] & "'" 
    If Not rs.NoMatch Then 
       Me.Bookmark = rs.Bookmark
    Else
       'The combo needs to be cleared at some stage
       Me![cboVesselName] = Null

       If MsgBox ("Do you want to create a new vessel?", _
          vbYesNo)=vbYes Then
          DoCmd.GoToRecord , , acNewRec
       Else
          'What to do ...
       End If
    End If
    
End Sub

 

[TimTang]

Hi Remou

I'm still getting the same message box.

Is there some way I can simulate closing and opening the form with code, then retrieve the new record and set focus to the second tab field?

That's what I'm doing now (manually)while I try to sort this out. It's just a pain in the A$$ to have to close form then reopen, find the new ship, and then start entering data.

Just an Idea!

 

[Remou]

You would not get the message box if you changed the Limit To List property of the combo to No. I tested this.


The way to simulate closing and opening the form is to requery, in order to do this with your present set-up you would have to go quite a long way around. It is not necessary to use a Not In List event unless you are using a look-up table (colours, countries, etc, etc).

 

[TimTang]

oohps! I forgot to change the Limit To List property of the combo to No

I've done that but now the new vessel isn't added to the Table

 

[Remou]

You are now in a new record, add the vessel then requery the combo in the After Insert event. If you wish, you can set the relevat control to the name that was entered in the combo. I hope the combo that you use to find records (Me![cboVesselName]) is not bound? It is not a good idea to have a bound combo that is used to search.

 

[TheAceMan1]

How are ya TimTang . . .

Be aware: The [blue]Response[/blue] arguement of the NotInList event [blue]doesn't activate until the event is complete.[/blue] When it activates, it performs its own requery of the combobox if [blue]Response[/blue] = acDataErrAdded. Within the event, when you requery the form, you requery the combobox, and since the event is not complete, the NotInList event is still detecting the change, hence your eternal loop!

To get around this, you allow the [blue]NotInList[/blue] event to complete by using the forms [blue]On Timer[/blue] event and performing the requery there. The code in your [blue]NotInList[/blue] then reverts to:

[blue]   Dim DB As DAO.Database, SQL As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   Set DB = CurrentDb
   DL = vbNewLine & vbNewLine
   Msg = "'" & NewData & "' is not currently in the list." & DL & _
         "Do you want to add it?"
   Style = vbQuestion + vbYesNo
   Title = "Unknown Vessel..."

   If MsgBox(Msg, Style, Title) = vbYes Then
      SQL = "Insert Into tblPositionsQuery ([VESSEL NAME]) " & _
            "VALUES ('" & NewData & "');"
      DB.Execute SQL, dbFailOnError
      Response = acDataErrAdded
      [purple][b]Me.TimerInterval = 25[/b][/purple]
   Else
       Response = acDataErrContinue
   End If
   
   Set DB = Nothing[/blue]

Then in the forms [blue]On Timer[/blue] event:

[blue]   Me.Requery
   [purple][b]Me.TimerInterval[/b][/purple] = 0[/blue]

Don't forget to set [blue]Limit To List[/blue] back you yes!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TimTang]

Back again!

Sorry I was pre-occupied with other things. I'm still trying to make this pig fly.

If I get time today I'll try your last suggestion.

When you say "After Insert" event, do you mean After Update? I already have code for the After Update event which populates the other fields. Can you have more than 1 sub for a single event?

(Me![cboVesselName]) is unbound. That seems to be what is making this process so agonizing.

This database is meant to be quick and dirty. I want to be able to input vessel locations very quickly. When a cargo becomes available I want to be able to locate a vessel close to the source which is open and the proper size and specification to handle the cargo.

Speed is every thing. The data will be worthless by the time it's 2 weeks old.

I hope you get a bit of an understanding of why I'm doing this database this way

 

[Remou]

I mean the After Insert event, it is the third one down from the top of the events tab in my version of Access.

The way you have of adding to the list is slow and difficult. The way I have suggested is quick. Consider, your table is like so:

Vessel
A
B
C

When a vessel is missing, you wish to add it:
D

The normal way to do this is to add a new record.

The combobox is merely a tool to look up these vessels, when a new vessel is added, it should be requeried, not the other way round.

 

[TimTang]

HI TheAceMan1

Thanks for the code! I'm finally getting there.

Your code works great but the only problem now is when the new vessel name is added, the other fields are defaulting to the first record in the table. I have to select another vessel then re-select the new one again and all the other go blank; ready for input just as it should be.

The good thing is I don't have to close and re-open anymore.

I think the new record just has to be refreshed somehow.

Thanks again for the code!

 

[TimTang]

Hi Remou

I'm using Access 2002, there is no "After Insert" for the combo but there is for the form; is that the one you're talking about?

I think what you're saying is correct but being a little inexperienced with Access I'm have a little trouble getting my head around it. I'll give it another try because I might be neglecting some proceedure and I was pretty busy during the week while experimenting with this.

Aceman1's code did work, in that the other fields are associated with the new vessel and ready for input. The problem is, you have to re-select the new vessel first and if you roll the mouse roller the other fields fly by but the vessel name remains static.

Maybe the answer is in both of your idea's combined somehow.

If there is any consolation, I've learned a lot from both of your contributions and maybe in the future I can help out others thanks to your help!

 

[Remou]

Yes, the after insert for the form.

 

[TheAceMan1]

TimTang . . .

It appears the remaining problem is [blue]going to the newly added record[/blue] after you requery (through the timer of course). Keeping that in mind the following should do:

[blue][tt]cboVesselName Modifications
***************************[/tt][/blue]
[ol][li]Your code in the [blue]AfterUpdate[/blue] event should've already been eliminated. If not, [blue]make it so![/blue] Actually, we'll be transfering the equivalent of this code to the [blue]On Timer[/blue] event.[/li]
[li]The [blue]Limit To List[/blue] property should be set to [blue]Yes![/blue].[/li]
[li]Add a [blue]private variable[/blue] to the declarations section of the forms module:

[blue]Private AddedVesselName as String[/blue]

[/li]
[li]Change the code for the [blue]NotInList[/blue] event to:

[blue]   Dim DB As DAO.Database, SQL As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   Set DB = CurrentDb
   DL = vbNewLine & vbNewLine
   Msg = "'" & NewData & "' is not currently in the list." & DL & _
         "Do you want to add it?"
   Style = vbQuestion + vbYesNo
   Title = "Unknown Vessel..."

   If MsgBox(Msg, Style, Title) = vbYes Then
      SQL = "Insert Into tblPositionsQuery ([VESSEL NAME]) " & _
            "VALUES ('" & NewData & "');"
      DB.Execute SQL, dbFailOnError
      [purple][b]AddedVesselName = NewData[/b][/purple]
      Response = acDataErrAdded
      Me.TimerInterval = 25
   Else
       Response = acDataErrContinue
   End If
   
   Set DB = Nothing[/blue]

[/li]
[li]Finally, change the forms [blue]On Timer[/blue] event to the following:

[blue]   Dim Cri As String, DQ As String
   
   DQ = """" 'Allows Single Quotes in VesselName
   Me.Requery
   
   'Goto Newly Added Record
   Cri = "[VesselName] = " & DQ & AddedVesselName & DQ
   Me.Recordset.FindFirst Cri
   
    'Turn Timer Off
   Me.TimerInterval = 0[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TimTang]

Hi TheAceMan1

I've been preoccupied with other things but I thought you should know your idea worked. I just had to add one line at the bottom to reassociate the other fields with the new vessel.

Dim Cri As String, DQ As String

DQ = """" 'Allows Single Quotes in VesselName
Me.Requery

'Goto Newly Added Record
Cri = "[VesselName] = " & DQ & AddedVesselName & DQ
Me.Recordset.FindFirst Cri

'Turn Timer Off
Me.TimerInterval = 0

Call cboVesselName_AfterUpdate

The only problem I've got now is that if you roll the mouse roller the other fields flash by with unrelated data while the Vessel remains the same.

It doesn't really effect the data in the table, but it would definitly freak out the person using the database.

I'm not sure how to stop that behavior.

Any ideas?

Thanks again for your assitance

 

[TimTang]

Hi TheAceMan1

I've been so busy because I'm learning a new job or career in ship broking. Last month I couldn't even spell ship brocer; now I am one.

I neglected to add "Private AddedVesselName as String" as you instructed, but it still works. I should try adding that.

What function does it serve?

Cheers!

 

[TheAceMan1]

[blue]I neglected to add "Private AddedVesselName as String" as you instructed, but it still works. I should try adding that.

[purple]What function does it serve?[/purple][/blue]

Since your going to [blue]requery[/blue] the form, [purple]you need a marker to return to[/purple] after [blue]requery[/blue] is done [purple]so you can return to the samr record![/purple]

I hope this is clear!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886