Some Simple Form Obstacles..I hope

[Rich_Lovina]

A couple of simple questions for a newby in Access Forms being designed for manual data input:
1.Where do I set in Properties for Titlecase, or uppercase (> ) and what is the Titlecase command?
2.Form is based on multi-table query. With RI intact in the related table (Instance type OR (in Maintablecode fld) for ‘Office Requisites’ (related table), this is okay if the link data exists. It appears I must turn RI off if I wanted to add OS - Office Supplies? I thought this is what “Cascade Update” is meant to allow?
3. Can I make a conditional drop-down list, and based on previous example, say I type OS and only related codes with the word “Office” appear in the drop-down e.g.
Typing OS DropDown yields OR-Office Requisites, or OI-Office Inventory. Or must this operation only be done in VB?

 

[Jack Cowley]

1. UCase() is the function to change text to upper case. StrConv("YourString", 3) is the function for Title case. Use them in the After Update event to change whatever is input to what you want.

2. From Access Help, "If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message."

3. This article for Access2000 will give you your answer (it will work for Access97 as well).
http://support.microsoft.com/support/kb/articles/Q209/5/76.ASP?LN=EN-US&SD=gn&FR=0&qry=q209576&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

 

[Rich_Lovina]

Tks Jack, but still not working. Oh this used to work for years so easily in Lotus Approach Forms !!!

1) UCASE turns all text in field to upper, yes? I just want to convert text after entry "mr" to "Mr" and "prof" to "Prof" or "capt" to "Capt" or "wgcdr" to "Wgcdr", so if I have to say a number in (string,3) that would only work for fixed character lengths?

2) The Access Help statement doesn't help, as I am NOT trying to change the primary key in the primary table, I'm trying to add a new record to that table from the Input Form. e.g. from the above example:

I want to enter my first instance of 'Professor' so in input form I type "Prof" as a new record for POSCODE (linked table, where fld poscode is primary key) and Poscode.title is the linked field where I type 'Professor' from the input form.
i.e. I should be able to enter a new record for the linked table from the input form, no?
But the Access prompt won't allow that record to be saved because it says it creates a null value record in Poscode.title field.

How also to carry forward data from some fields from the previously entered record to the next new record?

Sorry to have so many problems but these options are all done in Lotus by point & click options.

 

[SteveA]

Try StrConv([Enter Your String],vbProperCase). This function will convert the first letter of each word to a capital.

HTH
SteveA

 

[SteveA]

Sorry Pat,

I saw UCase mentioned and talk of the first letter being capitalised. I should have read through the full question properly before posting a reply.

SteveA

 

[Rich_Lovina]

Sorry to jump to conclusions based on my limited knowledge. I have not touched vb code and get easily confused.

First, I am sorry, I do not understand whether "yourstring" means type the fieldname or what.
I typed StrConv("YourString", 3) in a vb expression line and replaced "YourString" with fieldname salute. I saved the change and got "Syntax Error" message.

My HELP file for strconv talks all about ANSI and various complex codes for converting Japanese script and nothing about propercase.

HELP propercase says expression is proper (text). I tried this also to no avail.

Regret, can I please get this explained again as I only ever did Fortran IV at university 31 years ago....and that was a 3 mth course only.

 

[SteveA]

StrConv is a function that you would run as part of the AfterUpdate event of your field or in the BeforeUpdate event of your form. ie[Forms]![MyForm].[MyField] = StrConv(nz([Forms]![MyForm].[MyField]),vbProperCase)

I don't think there is a command such as '>' that you can place in the Format property of a field.

SteveA

 

[Rich_Lovina]

Steve, okay...thanks greatly yr additional patience as I try to understand some of this coding. (Oh I found the RIGHT helpfile in the VBHelp, but I was not to know that).

1) Now in the Properties box named AfterUpdate do I click ... then go to axpression builder and drop :

[Forms]![MyForm].[MyField] = StrConv(nz([Forms]![MyForm].[MyField]),vbProperCase)
in there?
and what does that little 'nz' do?

2) Format Uppercase in Access Help says use > only, but I can work thru this using above, I believe.

3) re: Allowing me to enter a new poscode (in Mytable.Myfield) in many side so's it updates to the 1 side which is MytableA.myprimaryfield I tried using the combobox (never used before) and all the steps seemed to further confuse me.
Above explanation from Pat still has me more confused.
Tks for patience...

 

[Rich_Lovina]

Re the above, I have tried dropping into expressionbuilder, and am getting the erroe message:
"The object doesn't contain the Automation object VbProperCase". So I'm getting closer..........tks....help!

 

[SteveA]

Okay. Here goes

1. I would click the ... and then choose Code. Drop the code into that section. The NZ() command checks to see if the field is null, if it is it will pass "" to StrConv instead of a Null value which can cause some functions to fall over.

2. Placing > in the 'Properties>Format' field will display your data in Uppercase but will not save it to the database in Uppercase. The option outlined in step one will actually save the value to the database.

3. If you have a dropdown box and want to add a new option, you cannot just type the value into the field. You must add the new entry to the recordsource of the dropdown box. Have a look at the code behind the drop down boxes in the Asset Tracking database ( a template database provided with Access 97 and 2000). This shows one way of handling it.

Let me know if I'm not on the right track.

Cheers,
SteveA

 

[Rich_Lovina]

1. Okay...........First time I've dropped code in vb that's worked!!!
But it appears it only works on the newly added record, which was my test piece.
Perhaps, very importantly, this thread reveals how significant it is to establish the extent of the enquirer's knowledge of any VB script !!!

I will now be able to go ahead and set some fields for Propercase and some for Uppercase, etc. for manual data entry, which is an important step forward for me.
Thank you very much Steve.

2. Use of > is understood too as I had that option in Lotus. the display only is useful when data being hacked into the one dbase from various sources and means (e.g. imports, OCR conversions AND manually dataentered...Great full flexibility.

3. Tks also for the tip re record source in Asset Tracking sample. I searched for samples thru Northwind without success.

One outstanding query I'll start in new thread as this is getting long-winded.