New to VBA - some help needed! (1 Viewer)

[TheShed]

Hi all, I am new to this fourm and new to VBA. I have been asked to develop a small database for my company. It's nothing big really and I'm sure an experienced VBA programmer could have it built in no time. Unfortuanatly, I am not one of those, but I am taking this on so I can learn a bit of it, and I am looking for some pointers

Ok, I have a table called mechanics which will simply hold a mechanics name along with an ID number which is generated using an autonumber. I have a form to allow the user to add a record into this table, it has a text box where they type the name and a button to submit it too the table, I and have coded it like this:-

Private Sub cmdSubmit_Click()


txtmechanicName.SetFocus
If txtmechanicName.Text <> "" Then
Call addMechanic
Else
Call errorMessage
Exit Sub
End If

End Sub

Private Sub errorMessage()
MsgBox ("You must enter a mechanics name!")
End Sub

Private Sub addMechanic()
Dim name As String
txtmechanicName.SetFocus
name = txtmechanicName.Text
CurrentDb.Execute ("INSERT INTO Mechanics (mechanicName) VALUES ('" & name & "') ;")

MsgBox ("Mechanic added!")
txtmechanicName.Text = ""
txtmechanicName.SetFocus

End Sub

Now when I hit submit, it is adding the record and the error message code is doing what I want it to so far, but I do have one little problem. Each time I add a record, it is also adding another record before it. So if I add a record with say the name "John Doe", it will be added along with say an autonumber of 2, but another record will be added before it with no name and an autonumber of 1.

Like I say, I have never coded in VBA before, but did do a very small bit of VB6 in college. So I am just wondering if I am going down the right path with this or is my code really wrong in the first place?

Any help/suggestions would be great! Cheers

 

[odin1701]

There is no need to use VBA to do this.

You can do this simply by having a text box on the form bound to your mechanic name field on the table. In the table all you need to do is set that field to required and they won't be able to enter a blank record. Of course, you can handle that error with VBA so they don't just get some standard Access error. I'm just not sure why you are trying to use VBA for this when it's a basic function of Access.

 

[TheShed]

There is no need to use VBA to do this.

You can do this simply by having a text box on the form bound to your mechanic name field on the table. In the table all you need to do is set that field to required and they won't be able to enter a blank record. Of course, you can handle that error with VBA so they don't just get some standard Access error. I'm just not sure why you are trying to use VBA for this when it's a basic function of Access.

Hi thanks for replying. You are right, the reason I want to use VBA is because I don't want some standard Access error message. The people who will be using it are not very computer-literate and I want it to be as simple and user-friendly as possible.

Also, some of the other forms I will need for this database will be a bit more complex and I think using VBA will give me more flexibility?


Plus I would also like to use this database to learn some VBA as I feel it is a skill I could use again in the future.

 

[macca the hacke]

Is the recordsource of the form the table "mechanics"? is the recordsource of the textbox txtmechanicname the field "mechanicname"?

If so, aren't you adding the record twice? Once by the text box being associated to the field, and then again because you have asked it to be added by using code?

 

[TheShed]

Is the recordsource of the form the table "mechanics"? is the recordsource of the textbox txtmechanicname the field "mechanicname"?

If so, aren't you adding the record twice? Once by the text box being associated to the field, and then again because you have asked it to be added by using code?

That was it, thanks mate!

 

[odin1701]

Is it the only field you need to be added? Or are there multiple ones?

If it's the only field, on a form with that field present you won't be able to add another record until you type something in the box. Though they could type and then delete.

If you want, you can do the following. If your text box control is named txtMechname then you can do this in your code:

Private Sub cmdSubmit_Click()

If IsNull(Me.txtMechname) Then

MsgBox ("You must enter a mechanics name!")

Else

DoCmd.GoToRecord , , acNext

End If

End Sub

 

[TheShed]

Is it the only field you need to be added? Or are there multiple ones?

If it's the only field, on a form with that field present you won't be able to add another record until you type something in the box. Though they could type and then delete.

If you want, you can do the following. If your text box control is named txtMechname then you can do this in your code:

Private Sub cmdSubmit_Click()

If IsNull(Me.txtMechname) Then

MsgBox ("You must enter a mechanics name!")

Else

DoCmd.GoToRecord , , acNext

End If

End Sub

Yes it is the only field. Well there is the mechanicID too, but I set that to an autonumber when I designed the table.

Thanks though, I will have other forms where that are more that one field being added, so thats a good tip

 

[odin1701]

Yeah basically the docmd.gotorecord is the same as clicking that next record arrow on the navigation on the form. The form will take care of adding the record so no need to add it in code.