Populating a Field in a Table from a form based on a query

[loonette]

I have been searching through this forum for hours and I can't find anything that will help me.

I have a query "qclerking" that is based on a table called "tblbiddersales", this is then popluated by entering information in a form called "frmbiddersales". There are several fields that information is entered into, but I have a field that populates itself called "ID". It populates from the "qclerking" query. I would like a field called "ID" to then be populated in the "tblbiddersales" after information is entered in the form.

I have tried to enter the following code in the "After Update" property, but it just populates the field with a "0".

Private Sub Text20_AfterUpdate()
Me.tblbiddersales = Me.txt20
End Sub

Any help will be appreciated. If you need any other information, let me know.

Thanks!

[Jerry Maiapu]

@loonette
Hi,
I am also new to access but I think you should use recordset to insert the ID into your tblbiddersales table. Its quite confusion here because a query only display or calculates based on the data from a table..and foan ID to populate automatically is not possible..

Check to see if the ID you are talking about a column in your table..

And maybe I can help..

Regards

Jerry

[NeoPa]

I'm not sure exactly what you're trying to say here, but let me guess, and you can tell me if I'm on the right lines.

You have a field [ID] in your [tblBidderSales] table.
You have a calculated field, [ID], in your [qClerking] query, which is reflected on your form [frmBidderSales].
You have a control on your form called [ID] which is bound to the calculated field in your query, but you'd like the value shown here to populate the [ID] field in your table.

Is that about the size of it?

[loonette]

@NeoPa

You described it exactly. Can this be done?

BTW, are you married? Cuz if you are your spouse is a very lucky person! LOL!

Thanks!
Loonette

[NeoPa]

It's sweet of you to say so Loonette :) My nick is derived from my son's gaming nick of Neo (or N30 to be precise), so yes. I've been married for some while now. Those who know me well tend to think that I'm the luckier one. I won't be so modest as to say my wife drew the short straw, but I probably drew a somewhat longer one.

As for your problem, it is certainly not too straightforward to handle, but it can be done. To start with, your query would need to have the [ID] field from [tblBidderSales] included, as well as the calculated [ID] field already there. They couldn't share the same name of course.

Next, you'd need to run some code to copy the contents of the calculated ID control into the table ID control at the appropriate time.

What that time is isn't too clear to me. Do you require each record to be updated as, and only as, you browse to the particular record? If each record needs to be updated, then we may be running on entirely the wrong lines here. That would probably require an update query to be run before browsing the data.