combining 2 fields (1 Viewer)

I'm working on a Db the main 1 table logs batch numbers and product details. The next table is for logging faults that occure to these batches. Table is linked by batch number. A serial No. is generated on this fault for consisting of first the batch No. followed by a dcount number that counts the number of fault records raised to that batch+1 to give a unique serial No. I.e. 11111/1. 11111/2 etc. I now need to put into the fault form a subform (actions) with multipul records.

My problem is how to link the 2. If i was to link the actions form by ID field to the fault form by batch number it would list all fault form actions for that batch. (multipul faults) I need to link the sub form to the unique fault form serial No.

My other thought was to combine the 2 fields to log as one the fault serial number logged to the fault table then add the relationship from this to the same ID in the action taable subform.

I've tried putting in this coding to join together the 2 parts and log the complete serialno.

Me.[comp_serial_no] = Me.[TSTL_JOB_NUMBER] & "/" & Me.[NPR_Serial_No]

Problem is its note writting to the [comp_serial_no] If I drop the Me.comp_serial_no from the coding it displays the batch No and the fault code together with no prolems but not saved to the table.

How do i get it to do this???

I've tried to explain this as best i can so hopefully you've got the jist of things.

cheers EQ.

i take it your form is bound to the table? and its saying you cant edit it?

rborob said:

i take it your form is bound to the table? and its saying you cant edit it?

Click to expand...

Thanks for replying to this and my other post.

Yes it is bound to the table. when you say i cant edit it i'm not sure what you mean. It comes up with a ?*Name (or something like that as my memory recalls) in the field box

Try this in the control source of your textbox.

=[TSTL_JOB_NUMBER] & "/" & [NPR_Serial_No]

kieth i've tried that. Whilst it does show what i want its not saving it to the table.

Right i've tried to go simple

In the control source of the text box 'comp serial no' I've placed the following expression.

=[TSTL JOB NUMBER] & [NPR Serial No]

this doesnt save the resulting figure (which is displayed in txt box) to the underlying table field called 'comp serial no'.

What am i doing wrong????????????????????????

This is doing my head in!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I'm obviously missing something very simple.

Are you sure both of the numbers you are trying to concatenate are actually numbers and the same type of number as the one you're trying to save to?

boblarson said:

Are you sure both of the numbers you are trying to concatenate are actually numbers and the same type of number as the one you're trying to save to?

Click to expand...

Thanks for replying bob. sorry for the delay in getting back i've been away. All 3 fields are infact text fields as occasionaly one of the numbers requires a letter on the end. I did have one set as a number so tried setting them all to text but again fails to write to the table???????????????

The control source you have will only display the value, not save to the table as it isn't bound (which is what the controlsource is used for). To do this, set the control source of the text box to the field in the table you want it saved to and then put a hidden text box on the form and put this in the controlsource:

And then in the AfterUpdate event of that hidden textbox, put

My problem is how to link the 2. If i was to link the actions form by ID field to the fault form by batch number it would list all fault form actions for that batch. (multipul faults) I need to link the sub form to the unique fault form serial No.

Click to expand...

cant you just join them in the underlying query?

Peter

Bat17 said:

cant you just join them in the underlying query?

Peter

Click to expand...

Being very new to building databases in access. Are you suggesting that in the subform i log the batch no. and serial no. then query it with the current forms batch number and serial no. to display only the subform records apertaining to this main record.

If so do i form a relationship between the batch number on the main table and say an ID auto number on the subform???? or the batch no. on the subform.

boblarson said:

The control source you have will only display the value, not save to the table as it isn't bound (which is what the controlsource is used for). To do this, set the control source of the text box to the field in the table you want it saved to and then put a hidden text box on the form and put this in the controlsource:

Code:Copy to clipboard

=Cstr([TSTL JOB NUMBER]) & Cstr([NPR Serial No])

And then in the AfterUpdate event of that hidden textbox, put

Code:Copy to clipboard

Me.YourBoundTextBoxName = Me.YourHiddenTextBoxName

Click to expand...

Bob

I have done the following

the text box has been bound to this comp serial no

I set up a hidden text box with the control source set to the following =CStr([TSTL JOB NUMBER]) & CStr([NPR Serial No])

and the after update code to the following

Private Sub hiddennprno_AfterUpdate()
Me.comp_serial_no = Me.hiddennprno

End Sub

The hidden text box displays the joined nos. But it is still not writing to the table field comp serial no???????

I've tried it twice now starting from scratch but no luck.

Hi EQ,

Try to add after update code to both linked field.

Private Sub TSTL JOB NUMBER_AfterUpdate()
Me.comp_serial_no = Me.hiddennprno
End Sub

Private Sub NPR Serial No_AfterUpdate()
Me.comp_serial_no = Me.hiddennprno
End Sub

It works for me!
Fan