How to add to Form Recordset?

[hwaclh]

I have a Form that is connected to a DB. The user enters 3 parameters and from that information I populate the rest of the database fields programatically. However when done I end up with 2 records. One that the form creates and the one I create programatically. I don't want this. I want to be able to work on the same Recorset. Any Ideas.

Thanks

 

[GingerR]

make the form unbound (no Record Source)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

hwaclh[b/]

GingerR's idea of of using unbound fields to accept the three parameteres will work. The added benefit here is that if the end user decides to abandon the entry, no partial record is created.

...I populate the rest of the database fields programatically.

If you are creating a second record, how are you programatically grabbing the primary key of the form when the three parameters are entered?

The way Access works with the AutoNumber being used as the primary key is that the autonumber is generated as soon as the end user enters data. In other words, the record is created (but not committed) as soon as data entry is started if you are using an AutoNumber field as the primary key.

There, when programatically creating the rest of the record, you have to do three things...
- Commit the record to the table. For example, use a "Refresh" command.
- You have to use the primary key, specifically, Me.NameOfPrimaryKey on your form.
- Update the record, not create a new record.

Richard

 

[hwaclh]

Thank you, you guys.

I have a lot of books on Access and I have been reading a lot.

I did found about the "unbound" form and I tried but when one unbounds the Form and tries to capture data all the fields only have "#name?" and they will not accept data.

I guess this opens to a new question. If that is an option how do I capture the data from the user in an unbound form?

I also found what I think is the answer to my problem but I just tried it and it didn't work. The whole form is Read-Only, so I must have missed something.

What I found is this

DoCmd, GotoRecord , , acNewRec
With Form
.orderId = int1
.ProductId = 1
.Qunatity = 1
.etc etc
End with

This is suppose to save the data to the Form and not to the RecordSet, correct?

Let me know if you guys what to see my code?

hwalch

 

[hwaclh]

Willir to answer to your question about the Autonumber. I don't have one.

What I am seen is this in more details.

I open the Form "Calendar" which has 3 Data entry fields
DownPayment, Amount, and Number of Payments.

The Form creates this record with these 3 fields. Then after I have these 3 fields I go in the backgorund and do all my math and find out all the payments. Now I want to put those payments back in the Form. In the back I create my DB connection which is to the same DB and table and if I do Edit or AddNew this is what creates the dupplicated record. That's why I need to work with the RecordSet of the Form not with a new of my own but it seems that Microsoft doesn't allow this.

Does this explains things better?

Thanks again

 

[willir]

To use GingerR's strategy, there are several ways to approach this. For example, use your normal form, and then add three unbound text or combo boxes. Then you can...
- Use the OnCurrent event procedure
- If a full record is displayed on the form, then you can "hide" the unbound text or combo boxes, or display ""
For example, if your primary key, called MyPrimaryKey in this example, is numeric...

If Nz(Me.MyPrimaryKey, 0) Then
Me.FirstUnboundCombo = ""
Me.SecondUnboundText.Visible = False
Else
Me.SecondUnboundText.Visible = True
End If

...Next
DoCmd, GotoRecord , , acNewRec

Is adding a new record. This may be why you are creating two records. A "Refresh" command is...
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
You can use the wizard for the command button to generate the code and place a "Refresh" button on your form.

As I stated, you need to use the Primary key and "edit" the current record. There are a zillion ways to do this. Perhaps this may work instead.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
With Form 
    .orderId = int1
    .ProductId = 1
    .Qunatity = 1 
    .etc etc 
End with

By the way, you seem to be quickly advancing well beyond the "intermediate" level of Access. Well done.

Richard