simple macro help

[jashgtp]

i have an variable which is zip code.

Then later on the form i have another variable which is just the first three of the zip code.

How can i setup a macro to have just the first three digits of the first variable fill in the later second variable.

These are on the same form btw.


Thanks in advance.

 

[Mike375]

Make the second one an unbound text box and use Left() function to grab the first 3 characters. Or it can be used in a new field created in your query and the field is displayed on your form.

If you are going to be doing this sort of thing then it will pay you in general to search around for Left(), Right() and Mid(). Combine those 3 with Len() and InStr() and you can manipulate entries in all sorts of ways.

 

[jashgtp]

mike i still would like these three digits to be saved in the table assoc. with the form....

can i do that using these functions?

 

[Mike375]

Yes you can do that.

You can use Left() to make a new field in your query and that will display the the first 3 characters.

Noiw make a simple simple macro using the SetValue action. The Item will be the name of the field where the data is to go and the Expression will be the name of the new field created in the query.

You could also do this by using the unbound text box containing the Left() and the name of the texbox would be the expression.

One disadvantage of the textbox is there is a small delay before the data appears. So if for some reason the SetValue was run as the formed moved to a new record the data would not yet be in the unbound text box.

You can also do it in bulk by using an Update query to the field where the data is to go and use the new field for the data.

As a side note it is usually best not to store such data. If you just display the result of the Left() then it will automatically update if for some reason you change the postcode.

Is your form based on a table or a query?

 

[jashgtp]

mine is based on form and tables only:


sorry im little unclear on what to do with the above steps..could you try and restate it to me.

THanks

 

[Mike375]

Do you know how to make unbound text boxes and simple Select queries?

 

[jashgtp]

i can make the text box...but i have not done a query before.

So that may be where i am having the problem.

 

[Mike375]

Well the Left() in the unbound text box can show the first 3 characters and a SetValue macro action will place the 3 characters displayed in the the text box to your field in the table. But you can't use the result in the unbound text box to bulk update the table. However, you could view the form in data sheet view and copy the column based on the unbound text box and paste it into the table field

But you should base your form on a query as queries allow so much to be done.

Apart from your current problem you will find that many of the answers given on the forums are based on a query being the record source for forms.