A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Bookmark: prevent infinite loop



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2008, 01:52 PM posted to microsoft.public.access.forms
BrunoDG
external usenet poster
 
Posts: 1
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.