Create new record button

G

Guest

Hi, Please help...

I am trying to create a command button that will add new record to my table
of data, and once the data is input i have a submit button to confirm that
all is well. I and the autonumber function to add the new record in the
table. I want to have a input box when I click add new record to add the ID
number of the person I am entering data for. This below is my code I have
which doesnt seem to be working for some reason...

Thanks, Jez

Private Sub btnCreateNewInput_Click()
varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblData (RPSGBRegNo) " & _
"VALUES ('" & varInput & "')"
varNewID = DLookup("max(FormNumber)", "tblData")
Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
"tblData.FormNumber = " & varNewID & ";"
Call UnLockAll
Me.txtFormNo.RowSource = "SELECT tblData.FormNumber,
tblData.RPSGBRegNo, tblData.Pharmacist, tblData.Pharmacy, " & _
"tblData.NACSCode, tblData.ContractorCode, tblData.Postcode " & _
"FROM tblData"
With Me
.RecordSource = sQRY
.RowSource = sQRY
Me.txtFormNo.Enabled = True
Me.txtFormNo.Requery
Me.txtDummy.SetFocus
Me.cmdSubmit.Visible = True
End With
End Sub
 
C

Carl Rapson

Jez said:
Hi, Please help...

I am trying to create a command button that will add new record to my
table
of data, and once the data is input i have a submit button to confirm that
all is well. I and the autonumber function to add the new record in the
table. I want to have a input box when I click add new record to add the
ID
number of the person I am entering data for. This below is my code I have
which doesnt seem to be working for some reason...

Thanks, Jez

Private Sub btnCreateNewInput_Click()
varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblData (RPSGBRegNo) " & _
"VALUES ('" & varInput & "')"
varNewID = DLookup("max(FormNumber)", "tblData")
Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
"tblData.FormNumber = " & varNewID & ";"
Call UnLockAll
Me.txtFormNo.RowSource = "SELECT tblData.FormNumber,
tblData.RPSGBRegNo, tblData.Pharmacist, tblData.Pharmacy, " & _
"tblData.NACSCode, tblData.ContractorCode, tblData.Postcode " & _
"FROM tblData"
With Me
.RecordSource = sQRY
.RowSource = sQRY
Me.txtFormNo.Enabled = True
Me.txtFormNo.Requery
Me.txtDummy.SetFocus
Me.cmdSubmit.Visible = True
End With
End Sub
Click to expand...

What do you mean by "doesn't seem to be working"? What part exactly isn't
working and what is it doing?

I can see a couple of issues right off:

You can't assume that an Autonumber field will always increase. The
Autonumber field can be anything, even negative, so your Max() method may
not always work. My preferred way of adding a new record that contains an
Autonumber field is to use the AddNew method of the Recordset:

Set rs = CurrentDB.OpenRecordset("SELECT * FROM tblData WHERE False")
rs.AddNew
varNewID = rs.Fields![FormNumber]
rs.Fields![RPSGBRegNo] = varInput
rs.Update
rs.Close
Set rs = Nothing

Once this is done, you can set your RecordSource as before.

Also, where did the variable sQRY come from in your With block? I don't see
where it is defined anywhere in the code you posted.

Carl Rapson
 
G

Guest

Carl,
Thanks for that, my problem is that once the input box has been click OK
with the input, it writes to the tblData, but then nothing else happens, I
cant input into any other box and no new autonumber appears in the
txtFormNumber.
I tried your code, this below is how I have written it and it still doesnt
work, it doesnt write to the tblData the input from the input box. Where can
I go from here?

Jez

Private Sub btnCreateNewInput_Click()
Dim rs As Object
varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
If varInput = "" Then
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE False")
rs.AddNew
varNewID = rs.Fields![FormNumber]
rs.Fields![RPSGBRegNo] = varInput
rs.Update
rs.Close
Set rs = Nothing
End If
DoCmd.SetWarnings False
'DoCmd.RunSQL "INSERT INTO tblData (RPSGBRegNo) " & _
"VALUES ('" & varInput & "') "
Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
"tblData.FormNumber = " & varNewID & ";"
Me.txtDummy.SetFocus
End Sub


Carl Rapson said:
Jez said:
Hi, Please help...

I am trying to create a command button that will add new record to my
table
of data, and once the data is input i have a submit button to confirm that
all is well. I and the autonumber function to add the new record in the
table. I want to have a input box when I click add new record to add the
ID
number of the person I am entering data for. This below is my code I have
which doesnt seem to be working for some reason...

Thanks, Jez

Private Sub btnCreateNewInput_Click()
varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblData (RPSGBRegNo) " & _
"VALUES ('" & varInput & "')"
varNewID = DLookup("max(FormNumber)", "tblData")
Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
"tblData.FormNumber = " & varNewID & ";"
Call UnLockAll
Me.txtFormNo.RowSource = "SELECT tblData.FormNumber,
tblData.RPSGBRegNo, tblData.Pharmacist, tblData.Pharmacy, " & _
"tblData.NACSCode, tblData.ContractorCode, tblData.Postcode " & _
"FROM tblData"
With Me
.RecordSource = sQRY
.RowSource = sQRY
Me.txtFormNo.Enabled = True
Me.txtFormNo.Requery
Me.txtDummy.SetFocus
Me.cmdSubmit.Visible = True
End With
End Sub
Click to expand...

What do you mean by "doesn't seem to be working"? What part exactly isn't
working and what is it doing?

I can see a couple of issues right off:

You can't assume that an Autonumber field will always increase. The
Autonumber field can be anything, even negative, so your Max() method may
not always work. My preferred way of adding a new record that contains an
Autonumber field is to use the AddNew method of the Recordset:

Set rs = CurrentDB.OpenRecordset("SELECT * FROM tblData WHERE False")
rs.AddNew
varNewID = rs.Fields![FormNumber]
rs.Fields![RPSGBRegNo] = varInput
rs.Update
rs.Close
Set rs = Nothing

Once this is done, you can set your RecordSource as before.

Also, where did the variable sQRY come from in your With block? I don't see
where it is defined anywhere in the code you posted.

Carl Rapson
Click to expand...
 
C

Carl Rapson

Jez said:
Carl,
Thanks for that, my problem is that once the input box has been click OK
with the input, it writes to the tblData, but then nothing else happens, I
cant input into any other box and no new autonumber appears in the
txtFormNumber.
I tried your code, this below is how I have written it and it still doesnt
work, it doesnt write to the tblData the input from the input box. Where
can
I go from here?

Jez

Private Sub btnCreateNewInput_Click()
Dim rs As Object
varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
If varInput = "" Then
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE
False")
rs.AddNew
varNewID = rs.Fields![FormNumber]
rs.Fields![RPSGBRegNo] = varInput
rs.Update
rs.Close
Set rs = Nothing
End If
DoCmd.SetWarnings False
'DoCmd.RunSQL "INSERT INTO tblData (RPSGBRegNo) " & _
"VALUES ('" & varInput & "') "
Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
"tblData.FormNumber = " & varNewID & ";"
Me.txtDummy.SetFocus
End Sub
Click to expand...
<snipped>

If you have reproduced your code accurately here, you are testing for the
wrong return from Inputbox. The test should be:

If varInput <> "" Then

Carl Rapson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error in Input Box 3
Create a button to save/update record OR add new record 21

Top