Help with vbYesNo msgbox

[focosi]

I have a custom made "New record" button on my form created using the button wizard. If I leave the code as the default :

Private Sub Comando513_Click()
On Error GoTo Err_Comando513_Click
DoCmd.GoToRecord , , acNewRec
Exit_Comando513_Click:


Exit Sub
Err_Comando513_Click:
MsgBox err.Description
Resume Exit_Comando513_Click
End Sub

.... everything works, but when I try modify as follows to get a confirmation message box and click yes ... :

Private Sub Comando513_Click()
On Error GoTo Err_Comando513_Click
Dim LResponse As Integer
LResponse = MsgBox("Hai già controllato se il ricevente esiste già ?", vbYesNo, "ATTENZIONE !")
If LResponse = vbYes Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.GoToRecord , , acLast
End If
Exit_Comando513_Click:
Exit Sub
Err_Comando513_Click:
MsgBox err.Description
Resume Exit_Comando513_Click
End Sub

a popup says me that I can't go to specified record and the debug highlights the line :

DoCmd.GoToRecord , , acNewRec

What's wrong with my code ??? I have googled for hours without result...

[June7]

I tested your revised code and it works perfect. Try running Compact & Repair. If still error, build a new button from scratch.

[focosi]

not so easy June7, I already tried both of them.
My error has something to do with runtime error 2105, but I cannot find the cause.....

[June7]

If I can't replicate the issue, can't advise solution. Do you want to make your project available for analysis?

[focosi]

Actually I have moved to a different strategy. My aim was to advice the user to check for existence of a given value in a field in order to avoid duplicates. Now I have found a better solution here exploting the NotInList event of my (search) combobox ... :

http://answers.microsoft.com/en-us/o...5-e4912f1bd1b0

... and I am trying to migrate this approach to my database.

Private Sub cboBank_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim lngBankID As Long
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "The bank, " & NewData & ", you entered is not listed!" & vbCrLf & "Do you want to add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblBanks (Bank_Name) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
lngBankID = DMax("[Bank_ID]", "tblBanks")
DoCmd.OpenForm "frmBanks", acNormal, , , acFormEdit, acWindowNormal, lngBankID
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub


This code is used to add a bank to tblBanks. The user types in the bank name and, if not listed, they are prompted to add it. The code then adds a record to the table and retrieves the Autonumber generated for the new record. It then opens a form passing the PK using the OpenArgs argument.



There is a companion code snippet in the On Open event of the Banks form:This piece looks to see if there is something in the OpenArgs (the newly added PK) and finds that record so the user can then enter additional data.


Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim lngID As Long
Dim rs As Object

Set rs = Me.Recordset.Clone
lngID = Val(Me.OpenArgs)
rs.FindFirst "[Bank_ID] = " & lngID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

Anyway even this code is giving me troubles since when I click "Yes" on the vbYesNo msgbox the code doesn't open a new record on the same form but rather prompts the default "not in list" Access msgbox. Any hint ?

[June7]

That is a good approach and code looks okay, except think need to requery the combobox so the RowSource is updated with the new entry. That's what I do in my procedure.

I am concerned about the use of DMax. Can there be multiple users for this db? If so and users are simultaneously adding new records DMax could return wrong value for users.

[focosi]

That is a good approach and code looks okay. Again, I doubt I can replicate the issue any more than with the other code so can't offer solution.

However, I am concerned about the use of DMax. Can there be multiple users for this db? If so and users are simultaneously adding new records DMax could return wrong value for one of the users.

yes, there are multiple users accessing DB over LAN... Any trick to avoid simultaneous addition of new records ? (pessimistic locking ?)...
And yes, I will have to post my code asap. Currently I have found advice on some forums that the bad behaviour could be due to required field on the table linked to the form not being filled... Will try to remove the requirements and will let you know.
Thanks for assistance.

[focosi]

hmmm, something better but not ok yet...
I have removed all requirements from the table linked to the form.
Now when I click "Yes" on the msgbox the new record is successfully created in the linked table but the forms stays on current record instead of moving to the newly created record as I would like. So I suppose the trouble should have something to do with :

lngBankID = DMax("[Bank_ID]", "tblBanks")
DoCmd.OpenForm "frmBanks", acNormal, , , acFormEdit, acWindowNormal, lngBankID

I just realized that the code I grabbed was aimed to open a form different from the one where the combobox is placed, while in my case the combobox and the records are shown on the same form. So there should be no need to pass arguments, right ? I have tried to replace DoCmd.OpenForm action with ... :

DoCmd.GoToRecord , , acLast

... but without success ("can't go to specified record" msgbox).
Any hint ?

[June7]

This is code from my project using the NotInList event. The combobox cbxStateNum is unbound. It is used for search only and an uneditable textbox is bound to the field.

Private Sub cbxStateNum_NotInList(NewData As String, Response As Integer)
Me.cbxStateNum = Null
Response = acDataErrContinue
If MsgBox("State Number not in database.  Add new project record?", vbOKCancel + vbQuestion, "NoRecord") = vbOK Then
    Me.cbxStateNum.LimitToList = False
    DoCmd.GoToRecord , , acNewRec
    Me.cbxStateNum.LimitToList = True
    Me!StateNum = NewData
    Me.tbxName.SetFocus
End If
End Sub

[focosi]

The point is that the structure of my form is different.
I have a table "tblX" with fields [ID] and [Name]
I have a form "frmY" with bound controls [ID] and [Name] and an unbound combobox whose row source is

SELECT [tblX].[Name], [tblX].[ID] FROM [tblX]

In order to search for Name within the unbound combobox I have to set :

Columns = 2
ColumnWidths = 0in;5in
Bound column = 2 ' which equals [ID]

In the AfterUpdate event for the unbound combobox there is an embedded macro

SearchForRecord
Record First
Where condition ="[ID] = " & Str(Nz([Screen].[ActiveControl];0))

Now the point is that I am not allowed to set LimitToList for the unbound combobox to False because the first visible column, determined by the ColumnWidths property, isn't equal to to the bound column. But I can't make [ID] the first visible column because the aim of the combobox is to search by Name, not by ID...
Any escape from this ?

[June7]

The problem for me is that you are using a macro - I use only VBA. Instead of the [Screen].[ActiveControl] reference, in VBA I would refer to the column index of the combobox to get the name criteria.
"[Name]='" & Me.comboboxname.Column(0) & "'"

The index starts with 0 so with your RowSource SQL the name is Column(0).

Now you can change the SQL to put the ID field first and bind to column 1 and the index reference for the Name would be 1.

In macro maybe Forms!formname!comboboxname.Column(1)

But really, even if user selects a row item by a name, why not still search for the ID of that item?