Adding

[Beany]

Hi,

im using an add button in a form to add new users...

im using the following code:

 Private Sub addrec_Click()
 Dim strSQL As String
 DoCmd.SetWarnings False
 Dim strmsg
 strmsg = "Are you sure you want to add " & username & " to the table?"

If MsgBox(strmsg, vbYesNo) = vbYes Then
   
 strSQL = "INSERT INTO tab_main ([username], [cost_centre], [number], [phone_model], [imei], [puk_code], [sim_no], [date_issued], [notes], [tariff], [call_int], [roam]) VALUES ('" & Me.username & "','" & Me.cost_centre & "','" & Me.number & "','" & Me.phone_model & "','" & Me.imei & "','" & Me.puk_code & "','" & Me.sim_no & "',#" & Me.date_issued & "#,'" & Me.notes & "','" & Me.tariff & "'," & Me.call_int & "," & Me.roam & ")"
 ''Debug.Print strSQL
 DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True
 End If
 End Sub

But the problem is sometimes it adds, sometime it doesnt......

i dont understand why?

it wont add at all sometimes

[technocraze]

wht nt try rs.AdddNew

[Beany]

wht nt try rs.AdddNew

sorry how do i use rs.AdddNew?? or where do i insert it?

[nico5038]

I would guess it's an illegal value for one of the fields. In general ForeignKeys and or duplicate values for the PrimaryKey are causing this, or empty fields that need to be filled according the table definition. (zero length allowed = no)

Best to place a break on the assigment statement for strSQL and test with a know failing record.
When the code halts use the immediate window to enter:
?strSQL
and press enter to get the statement as it would be executed.
Then copy/paste the SQL into a query and run it to see the error message.

Nic;o)

[NeoPa]

The first thing I would do in your position is to comment out your

'DoCmd.SetWarnings False

line.
This will have the effect that you can see the warning code that Access should display when it fails.
This will give you more information to start with.

Setting the Warnings off is a good idea for polished looking code, but doesn't help much during the development stage. It's a better idea to add this in when all testing has been completed.

[NeoPa]

Another idea when starting out in Access, is to use the automatic ability of Access forms to add new items. This isn't brilliant in all circumstances but should do for most. It has the benefit that there is very little (if any) extra design or code needed to get it up and running quickly.