Linking a textbox with another textbox as control source to a table field

[IanMilly]

Hi,

I have a textbox in a form which is linked to another textbox in the same form to extract the first two letters of the first text box (using control source, ([textbox1],2)). What i want to do is to be able to link the textbox2 (the two letter value extracted from a bigger value) to a table in order to create or read these values. I will be able to use this inforamtion to query a new input value etc. Is this possible to do?


Thanks

Ian

 

[Mile-O]

You don't store the first two characters - that would be a design flaw

As you want to use the first two characters in query, just use:

NewField: Left([Forms]![MyForm]![MyTextBox], 2)


to get the first two letters within the query and scrap the unnecessary textbox.

 

[Mile-O]

On another note - don't bind a table to form. Make a query of your table with only the records you need and bind that to your form.

 

[IanMilly]

On another note - don't bind a table to form. Make a query of your table with only the records you need and bind that to your form.

Thanks, sorry for the simplicity of the question, i am fairly new to access and am trying to develop new ideas for my db all the time.

thanks for the quick reply and the good info

Ian

 

[Mile-O]

For the best info - search and read up on normalization and the normal forms.

 

[IanMilly]

For the best info - search and read up on normalization and the normal forms.

Hi,

I have looked for this but with not a lot of joy.

Can you tell me where I should put the code please? I have tried in the query in the field name but it treats it as an expression and adds the value of whatever i type in to every item in the field.

Kinda stumped on this one. Thanks

Ian

 

[Mile-O]

Have a look at this quick demo.

As for normalization, I'd be surprised if you found little information as Google returns about 963,000 pages as a result of a search.

 

[IanMilly]

Hi ,

Thanks for that, was missing the field name in the title.

The first two letters in the form are now taken into the query. However the value of the first two letters is placed in every item in the field, not as an individual case . ie when the number is ao12345 for item 1, the whole field in the query shows ao for all items in the letter field. When item 2 is applied, with letters DI, then the whole field in the query (letters) is changed to DI (overwriting previous AI).

Any ideas?

Thanks

Ian

on the normalization search i think i was being too specific and coming up with some v random info. Just checking again with minimal search words came up with several info source (917000 -prob of which 900000 are porn sites though )

 

[Mile-O]

prob of which 900000 are porn sites though )

And the problem there is?

 

[Mile-O]

The first two letters in the form are now taken into the query. However the value of the first two letters is placed in every item in the field, not as an individual case . ie when the number is ao12345 for item 1, the whole field in the query shows ao for all items in the letter field. When item 2 is applied, with letters DI, then the whole field in the query (letters) is changed to DI (overwriting previous AI).

Can you post a physical example of what you mean as I can't visualise this?

 

[IanMilly]

Hi,

thanks for this. attached is part of the db that i am talking about. Changing the cardtype in the form changes the first letters of the serial num. The letter is sent back to the query, changing all values -have a look and see.

Thanks

Ian

 

[Mile-O]

A few changes - new table added and form is now bound to a query instead of a table.

 

[IanMilly]

thanks, that is v useful.

Just a quick question, what is the query qryObjects being used for?

thanks

Ian

 

[Mile-O]

qryCards is a query of your cards table and sorted by the name so that it appears Ascending

qrySerialNum is a query of your tblSerialNum for use when binding to the form

qryObjects was a copy of the original query you had but I've never finished it when I uploaded.

If you open the query, qryObjects, and then switch from either Design View or Datasheet View to SQL View then replace all the SQL with this:

SELECT tblSerialNum.SerialNum, tblSerialNum.Mandate, tblCards.Card, Left(Forms!frmSerialNum!txtBarCode,2) AS Letter
FROM tblSerialNum INNER JOIN tblCards ON tblSerialNum.CardID = tblCards.CardID
ORDER BY tblCards.Card DESC;