469,271 Members | 1,206 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Adding a record on a subform using data from an unbound text box


I have a form called Customer whose record source is a table called Customer. The primary key is Customer_ID.

On that form, there's a subform called sub_Customer_Contact_Log. It's record source is a table called Customer_Contact_Log. It's primary key is Customer_Contact_Log_ID.

The subform is linked to the main form by Customer_ID (which is in both the Customer table and the Customer_Contact_Log tables).

I've put an unbound text box on the main form. It's called txt_Log_Entry.

I put a button on the main form. On the click event of the button, I want to take what's in the txt_Log_Entry text box and create a record in the Customer_Contact_Log table. There are 4 fields I'd like to update:

Customer_Contact_Results = txt_Log_Entry
Customer_Contact_Date = now()
Customer_ID = the Customer_ID from the main form
Customer_Contact_ID = since this is an autonumber field, I imagine this just gets updated by adding the record, but for thoroughness I included it

And after I add the record, I want the sub form to show the new records (either a refresh or a requery, not sure which).

I've tried a variety of things, but am fairly new to this and can't make it work right. I'm sure it's quite easy and any help you can provide would be greatly appreciated.

Sep 24 '09 #1
4 3532
32,171 Expert Mod 16PB
Firstly, if you get an infraction PM you can ignore it. I misread your field settings as code. I removed it from your record as soon as I read the post more fully.
Sep 24 '09 #2
32,171 Expert Mod 16PB
As general help check out Referring to Items on a Sub-Form, paying particular attention to what a SubForm control is (it's isn't - but does contain - an Access Form object).

To get more specific help I suggest you post your best version of the code that you've tried, explaining where it fails. We can work from there, but avoid simply providing code from scratch. It's obviously much harder if we have no idea what stage you're at.
Sep 24 '09 #3
I'd been trying to do this with a sql statement. Alternatively I tried using a DAO recordset and that seemed to work. Here's the code I used for anyone's reference. Would be curious if this was the best way or if I have anything superflous.

Expand|Select|Wrap|Line Numbers
  1.    Dim dbCustomer As DAO.Database
  2.    Dim rstCustomer_Contact_Log As DAO.Recordset
  4.    Set dbCustomer = CurrentDb
  5.    Set rstCustomer_Contact_Log = dbCustomer.OpenRecordset("Customer_Contact_Log")
  7.    rstCustomer_Contact_Log.AddNew
  8.    rstCustomer_Contact_Log("Customer_ID").Value = [Customer_ID]
  9.    rstCustomer_Contact_Log("Customer_Contact_Date").Value = Now()
  10.    rstCustomer_Contact_Log("Customer_Contact_Results").Value = [txt_Log_Entry]
  11.    rstCustomer_Contact_Log.Update
  13.    Me![sub_Customer_Contact_Log].Requery
Sep 25 '09 #4
32,171 Expert Mod 16PB
Without the context (the procedure start and end code) it's harder to read what's going on where, but I'm guessing this runs somewhere in the main form.

I would query line #13 as it doesn't seem to requery the subform properly, but Access has a number of shortcut ways that work as well as the standard way so it may. If you say it does I'm sure it does.

Otherwise, the code looks as succinct as you could hope for. I see nothing wrong at all (Putting .Value after the field references is superfluous as it's the default property anyway, but that's just being picky, and it may simply be you've chosen that style anyway).
Sep 25 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Tim Marshall | last post: by
7 posts views Thread by ChrisR | last post: by
20 posts views Thread by GAVO-UK | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.