VB access incrementing a text box

[jonhanks]

I'm fairly new to access and VB. I have a form which contains several
text boxes and an insert record command button. The button is supposed
to insert a new record into the database using the text fields, and
then increment a text field by 1. The insert record part works fine,
but I cannot seem to increment a text box by one. I'm sure theres a
simple explination to this!! Here is the code behind the command
button.

Private Sub cmdInsertRecord_Click()
Dim conDatabase As ADODB.Connection
Dim strSQL As String

Set conDatabase = CurrentProject.Connection

strSQL = "INSERT INTO PHOTOSAccess (REF) VALUES (" & lblRef & ")"
conDatabase.Execute strSQL

conDatabase.Close
Set conDatabase = Nothing
[Forms]![enterRecord]![lblRef] = [Forms]![enterRecord]![lblRef] + 1

End Sub

Its the last statment i cannot get right, any ideas? The text box I
want to increment is called 'lblRef' and is on a form called
'enterRecord'.

many thanks

jon.

[Rich P]

Try it this way. I will assume that "enterRecord" is the name of the
form that your command button is on. If this is the case, you can
substitute

[Forms]![enterRecord]![lblRef]

with the keyword "Me"
Sub ...()
'your other code
...
Set conDatabase = Nothing

Dim i As Integer
i = Me![lblRef] + 1
Me![lblRef] = i
Me.Requery
Me.Refresh

End Sub

Note: if lblRef is a textbox you should refer to it as

txtRef

lblRef indicates a Label control. Comboboxes are referred by
cboSomething, forms use frm for a prefix, frmEnterRecord,
Queries, qryRef, Tables, tblPhotosAccess.

The goal with the prefixes is to reduce/eliminate ambiguity.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[jonhanks]

Hi

Thanks for the suggestion.Unfortunately Its bringing up an error:

'run time error 6 over flow'

and then debug points to the line
i = [Forms]![enterRecord]![txtRef] + 1

cheers

jon.

[jonhanks]

Hi

Thanks for the suggestion.Unfortunately Its bringing up an error:

'run time error 6 over flow'

and then debug points to the line
i = [Forms]![enterRecord]![txtRef] + 1

cheers

jon.

[Rich P]

If you are working directly in your form you don't even need the "Me"
keyword. Use your debugger to see what is in your textbox

Sub ....
...
Debug.Print "*" & txtRef & "*"
...
End Sub

I use "*" delimiters to catch any white space. If the debugger shows
something different than a number or a number plus some invisible
character, then yes, you will get an error trying to add it to an int
var. So you may want to trap for this as follows:

If IsNumeric(txtRef) Then i = txtRef + 1
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[jonhanks]

thanks for your help guys, much appreciated. All sorted now!