INSERT setments

[santanudas]

Hi all,
I got a little problems with 'INSERT'. I have a small ‘INSERT’ code to input data in a table (tblBooking):

Private Sub cmdSubmit_Click()

Dim strSQL As String
strSQL = "INSERT INTO tblBooking (dept, day, month, year, timing, room) VALUES ('" & Me.txtDept & "', " & Me.cmbDay & "," & _
"" & Me.cmbMonth & ", " & Me.cmbYear & ", " & Me.cmbTiming & ", " & Me.cmbRoom & ""

'Print out the value of the strSQL to the immediate window for review to verify it is accurate
MsgBox strSQL
Debug.Print strSQL 'Run the SQL
DoCmd.RunSQL strSQL
Me.txtDept = ""
Me.txtDept.SetFocus

End Sub

This also working fine but when I’m inserting record first time in a totally blank table, it’s inserting the first (only) record set twice. After that as usual. Any idea?

Another question: I have a 'autonumber' field in this table. After deleting all the rows again when I start inserting data, the id (autonumber) fiels is not being started from 0; it's starting from the next number up to which I deleted. like, if I delete all 3 rows to make the table empty then next 'insert' being started from id=4 not from id=0 like a brand new table. Any idea about how to fix that?

Thanks in advance.

Santanu

 

[scriverb]

As for the autonumber, Yes, deleting records do not start the numbering over. The AutoNumber field is unique and will continue to increment by 1 even with deletions. But, you can reset it by performing a database Compact utility when the table is empty and has no records.

As for the rest of your code, I have duplicated your table and your form with the code provided. It works perfectly whether you have zero(0) records in the table or many records. I could not get it to duplicate the process of doubling up on the Insert of a single entry on the form. My suggestion would be to empty the table and put a STOP command right after your Dim statement. This will stop the code there and you can walk through the code one line at a time using the F8 key and see if somehow you code is looping and reinserting the same data. F8 until you get to the End Sub. There should be no way then that the records are doubled in the table.

Post back with questions if you have any.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[santanudas]

Hi scriverb,
Many thanks for your help. But unfortunately (and for some unknown reason) still the very first row is being duplicated. I think I've to chk more carefully. But I’ve got two other questions:

How to go to the next form from the present one? Like, pressing a button (say “Next”) will close the current form and will open another (say frmForm2) form?

How to export a field value from one form to another? Like if I open a frmForm2 from frmForm1 then the value of frmForm1.field1 will be value of frmForm2.fielsX when frmForm2 opens.

Thank you once again.
Santanu

 

[scriverb]

Use the following code behind a command button to open another form and pass a value through the Arguments parameter:

frmForm1
DoCmd.OpenForm "frmForm2", , , , , , Me![FieldX]
DoCmd.Close acForm, "frmForm1"

frmForm2 - On Open Event Procedure
Me.FieldXX = Forms!frmForm2.OpenArgs

This code for frmForm2 passes the OpenArgs property to a control on frmForm2 the value from FieldX of frmForm1. This is just an example as you can set a variable with this value or use it in an expression.

Post back if you have any questions. Also, on your duplicate record problem I will keep thinking about what may be causing your problem. Did you walk(step) through your code as I suggested? Did it duplicate it at that time also?

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[santanudas]

Hey scriverb,
Thanks very much. I just modified the code a bit like this:

Dim chkDay, chkMonth, chkYear As String

chkDay = Me.cmbDay
chkMonth = Me.cmbMonth
chkYear = Me.cmbYear

DoCmd.Close
DoCmd.OpenForm "frmBookEntry"
[Forms]![frmBookEntry]![txtDay] = chkDay
[Forms]![frmBookEntry]![txtMonth] = chkMonth
[Forms]![frmBookEntry]![txtyear] = chkYear

and working fine. Another question: I need to generate a dynamic combo box (values from a table “tblRoom”) based on the value from another combo box. I’ve written some thing like:

Private Sub cmbBook_type_AfterUpdate()
If Me.cmbBook_type.Value = 1 Or Me.cmbBook_type.Value = 2 Then
Me.cmbRoom.Enabled = True
Me.cmbRoom.RowSource = "SELECT * from [tblRoom] WHERE [for] = 'M';"
Else: Me.cmbBook_type.Value = 3 Or Me.cmbBook_type.Value = 4
Me.cmbRoom.RowSource = "SELECT * from [tblRoom] WHERE [for] = 'S';"
End If
End Sub

But no joy. Any idea?

Thank you once again,
Santanu

 

[sdimov]

Your AutoID is immaterial to what you are doing.
Why do you need to start from zero? If, for some reason you do, then you have to just use a regular number format and populate it yourself.