Populating table field from a text box on a form

[traceya]

Using Access 2007

I am using a form (fmProducts) to populate a table (tblProducts)
On the form I have a disabled text box (tbProductCode) which is populated using an expression.
I need this information to be added to the relevant field (ProductCode) in the associated table but this isn't happening.

How do I get this to happen automatically as I don't want users to be able to access the table directly.

I have very little vb knowledge so if I need to use this could you please explain how to do it in simple terms

Thanks

 

[Trevor G]

Welcome to the forum, try this out.

Open your form in design view
Select the textbox tbProductCode and open the properties
In the protperties click the Event Tab and then try the Before Update Event and then click the elipsee button and select Event Procedure
In the VBA screen type in me.ProductCode=tbProductCode

Save the form and close the VBA screen down
Change the view of the form and then move to a record or two then close the form and go to the table and see if it has populated the 2 records.

 

[traceya]

Hi Trevor

I've tried this but it doesn't populate the field in the table.
The expression in the disabled text box is:
=[cboSupplierCode] & "-" & [cboCategory] & "-" & [ID]

This combines the 3 previous fields on the form to give the ProductCode in the text box. This is the field that isn't being sent to the table.

As per your advice, this is what I've entered in the vb screen:
Private Sub tbProductCode_BeforeUpdate(Cancel As Integer)
Me.ProductCode = tbProductCode
End Sub

Any ideas?
Thanks

 

[boblarson]

I don't think the update events of the control is going to work with the control source, if I remember right. Because I don't think it actually fires either of the update events. So, what you can do is to use the code in the FORM'S BEFORE UPDATE.

 

[boblarson]

And Actually, I forgot to mention that if these fields exist within the same table, you should NOT be saving this information to the table in another field anyway. You should put it together as needed during RUNTIME.

The mistake people make is thinking they need to use a table for everything when a query can be used in about 99% of the places a table can be used.

 

[traceya]

Hi Bob
Tried putting Trevor's code there too and it still doesn't work......
Any ideas?
Thanks

 

[traceya]

OK now I'm lost?
How do I add a query to the form?
Thanks again

PS: This self taught stuff is harder than it looks

 

[boblarson]

OK now I'm lost?
How do I add a query to the form?
Thanks again

PS: This self taught stuff is harder than it looks

You don't need to change anything about what you currently have. What you have to change is your expectation that the data should be saving in the table. If all of the fields which make up that concatenated display in the disabled text box are in the table, you do not store that data from the disabled text box. When you need it later on down the line, you create a query for your use and you don't use the original table as the main source. For example, if you needed it on a report, instead of selecting that table for the report you would select your query which you will write which will have and extra field at the end of it which concatenates it together like you have done with the control source of the disabled control.

And just as an FYI you can change the form's record source to a query from a table like this:
http://www.btabdevelopment.com/ts/chrs

 

[Trevor G]

Hi Trevor

I've tried this but it doesn't populate the field in the table.
The expression in the disabled text box is:
=[cboSupplierCode] & "-" & [cboCategory] & "-" & [ID]

This combines the 3 previous fields on the form to give the ProductCode in the text box. This is the field that isn't being sent to the table.

As per your advice, this is what I've entered in the vb screen:
Private Sub tbProductCode_BeforeUpdate(Cancel As Integer)
Me.ProductCode = tbProductCode
End Sub

Any ideas?
Thanks

Take on board Bobs advice you should use a query which will show you the calculation and you can then use that as your source for your report

 

[traceya]

Hi Bob

I have used the link you sent to build a query and used this as the record source for the form. However, the rest of the fields on the form are now showing error messages. The original record source of the form was tblProducts. Should I revert back to this as the form's record source and, if so, how do I relate the disabled text box to the query I have built?
Thanks again

 

[traceya]

Might be getting myself into knots.......

My query has:
SupplierCode, Category, ID from Product table
and Expression 1 =[SupplierCode] & "-" & [Category] & "-" & [ID]

When run it returns the correct ProductCode

What I need to do is add the relevant row to either the Products table or the Products form.
Would I do this using a "where" statement i.e. where tblProducts[ID] =qryProducts[ID]

If so what is the correct syntax for this and where would I put it to add the correct result to the form?

Thanks

 

[boblarson]

Didn't mean for you to do it in this form's record source. I meant in other forms and reports. If you put it in this query then in becomes non upgradable. You can just display it on this form by using a formula in a text box.

Sorry to get you all confused.

 

[traceya]

Hi Bob
Thanks for getting back to me.

I've added the original formula back to the text box on the form as this worked OK and displays the correct result on the form.

Eventually I want to be able to pull up a report from the Product table which will show all the product codes for a specific supplier or category. If I add the ProductCode query to the report do I need to add a 'WHERE' clause to the syntax (see above comment)? If so, how should it be worded?

I'm getting there......albeit very slowly

Many thanks
Tracey

 

[outrigger]

You make a query off the table, in the query you concanate the three fields you were talking about into a new field with the name you choose. You can place a [Enter] statement into the criteria of any field you want to filter the report on and use this query as the report control source. I pretty sure that is what Bobwas trying to explain. Your form will fill your table except for the concanated field, that will happen in the report off the query.
Outrigger