If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Bookmark: prevent infinite loop
Hello,
I've created an Access ADP (2003) that connects to a SQL Server 2000 database. On one of my forms I use a button to create a new record (company). The user is asked to enter a company name (using an inputbox). Next, the record is added with an INSERT command. After the record is created, I want my form to go to the created record. I've used the bookmark property and everything seemed to work fine ... This is my current code (remark: my primary key is a Unique Identifier): On Error GoTo Err_Nieuw_Click Dim strInput As String Dim strSQL As String Dim RS As ADODB.Recordset Dim dbs As ADODB.Connection Dim rst As ADODB.Recordset Dim strLink As String Dim ctl As Control Dim strGUID As String Dim ctlnew As Control Dim strGUIDnew As String Dim c As Long Form_frmFirma.AllowAdditions = True strInput = InputBox("Enter the name of the new company:", "Add new company") If strInput = "" Then GoTo Exit_Nieuw_Click End If 'strSQL = "TRUNCATE TABLE GUIDTemp" 'DoCmd.RunSQL strSQL Set dbs = New ADODB.Connection dbs.ConnectionString = CurrentProject.Connection dbs.Open Set rst = dbs.Execute("EXEC dbo.spToevoegenGUID 'Firma'") If Err.Number = 0 And rst.State = adStateOpen Then strGUID = rst("@@GUID") ' This returns the Unique Identifier of the newly created record/company End If strSQL = "INSERT INTO tblFirma (FirmaID,Firmanaam, VertegenwoordigerID) SELECT '" & strGUID & "','" & strInput & "', '" & strUser & "'" DoCmd.RunSQL strSQL Form_frmFirma.Requery General.lngPogingen = 3000 ' Retry 3000 times before showing a message box (else: an infinite loop may occur). This value can be changed to work on faster/slower computers. Retry: Set RS = Me.Recordset.Clone RS.MoveFirst RS.Find "[FirmaID] = '" & strGUID & "'" If Not RS.EOF Then Me.Bookmark = RS.Bookmark Set ctlnew = Me.FirmaID strGUIDnew = StringFromGUID(ctlnew.Value) strGUIDnew = Right(Trim([strGUIDnew]), 39) strGUIDnew = Left(Trim([strGUIDnew]), 38) If strGUID strGUIDnew Then If c General.lngPogingen Then c = c + 1 GoTo Retry Else MsgBox "The original record could not be found. Contact your administrator for more information.", vbOKOnly, "Record not found" End If End If Exit_Nieuw_Click: Exit Sub Err_Nieuw_Click: MsgBox Err.Description Resume Exit_Nieuw_Click Everything works fine on my computer(s) but doesn't on my clients computer(s)! The message box is shown almost every time. I've done a lot of debugging but I can't find the reason the record can't be found. Any ideas? Regards, Bruno |
Thread Tools | |
Display Modes | |
|
|