Automatically adding a record to a subform for a new record

[Sydney1]

Hi,

I have the following code in the after update event of a combo box

Case "bka"
        Set MyDB = CurrentDb
        Set MyTable = MyDB.OpenRecordset("tblLoanSteps")
        MyTable.AddNew: MyTable("litigationID") = Forms!frmLitigationEntry!txtLitigationID
        MyTable("ScheduleDate") = Date: MyTable("Steps") = "1"
        MyTable.Update
End Select

which adds a new record to a table (which is also the form's subform. It works fine, but how would I go about using this code on a new record in the main form. Right now I have to enter the data in the main form and then get off of and then back on the new record and then the code adds a new record to the subform. I've tried to save the record in the code, but that didn't work.

Thanks for your help

Sydney

 

[GingerR]

Hi. I'm a little unclear.
You're on your Main Form. You pick something from a combo box? Is that your first activity?
Once you pick something from the combo box, a new (blank?) record is added to the subform with the LitigationID that is on the Main form. right?
When you type into the Main form, a new record is being added there. I'm confused by your statement "how would i go about using this code on a new record in the main form"? You want to add another record with the same ID to the main table? Is it a primary key?

Again, sorry but I'm confused. If you could fill me in a little more that would be great.

thanks.

 

[Sydney1]

Hi,

Thanks for the reply and sorry for not being clear.

The backend table is SQL Server 2000.
Litigation ID is the autonumber (Identity) for the main form. LoanstepID is the autonumber for the subform. The combobox is the third field in the main form to be entered.

What appears to be happening is that the main form's record needs to be saved and refreshed (going to another record and back to the new record does this) before any records can be automatically entered into the subform (or it's table). I would like to be able to automatically enter data into the subform without having to manually save the main form's record.

Again thanks for your help

Sydney

 

[GingerR]

Try picking the combo box. don't go off the main form. Go and check the table that feeds the subform. Did a record get added? Check the table that feeds the main form. Is the new record there with the newest ID?

 

[Sydney1]

Hi,

The record appears in the subform's table with the LitigationID field null. The main table's record does not appear in the underlying table until I move off the record. Also, the autonumber field (LitigationID) remains blank in the form until I move from the new record.

Thanks again for your help

Sydney

 

[GingerR]

ok so....You open the Main Form. You enter some data. The autonumber control is not getting filled in as you begin to type? Is the Main Form's recordsource the Main Table? Or is it an unbound form?

 

[Sydney1]

Hi,

It's bound to a query that includes the main table and one other table.

Thanks again,

Sydney

 

[GingerR]

so when you start typing into the Main Form, does the Autonumber control on the form populate right away?

 

[Sydney1]

No. It doesn't fill in until I move off the record.

 

[GingerR]

I don't understand that. Create a new form based on the main table. put the ID field (autonumber field) and another coupla fields on the form. type data into one of the fields. The autonumber field should populate.

Maybe it's because it's sql server?

Anyhow, try adding a button from the toolbox, and use the wizard to do RECORDS + SAVE. see if that works. If so, you can put the code into your combo box's code, before the other code.

 

[Sydney1]

Hi,

Sorry it took so long to get back, but my internet connection was down. It may very well be the SQL Server table. I've tried the save command in the code, but that doesn't fill in the autonumber either.

Thanks again for your help

Sydney