using vba to create a new record

[ronaldff]

Hi everybody
First I hope this is the right spot for this thread.

I would like to load a record into a user form from my database table, read the data from the form into a VBA procedure, manipulate this data, and then create a new record from the manipulated data.

Manipulated in this context means simply to recalculate some values, change dates and text entries. I could of course use the form's duplicate facility to create the new record, and do the recalculations in the form - but using VBA seems more elegant!

So far I have read the data fields from the form into the VBA procedure, done the recalculations and made the date and text changes - but I am now stuck! How do I create this new record?

Many thanks in advance!
Ron

 

[CJ_London]

run an insert statement using currentdb.execute "INSERT...."

 

[ronaldff]

hello CJ_London
many thanks for your response - I have moved forward. I used this code

    CurrentDb.Execute " INSERT INTO test_table_01 " _
        & "(item_01,item_02,dbamnt,cramnt) VALUES " _
        & "('ross', 'poldark', '899.45','777.23');"

to append a new record into my table - works OK. However, I have failed to get my variables read in from the form into the above bit of code. More help would be appreciated.
Ron

 

[CJ_London]

first off, you need to get your data types right

assuming '899.45','777.23' are appended to a double or currency datatype then you do not need the single quotes

t5o get data from your form you would do something like this for VALUES

.....VALUES ('" & me.txtitem1 & "', '" & me.txtitem2 & "', " & me.txtVal1 & ", " & me.txtVal2 & ");"

where txtItemX is the name of your control where you want to get the data from. Note the different treatment for the first 2 items which are text and the last two which are numeric

 

[ronaldff]

hello CJ_London

many thanks again for your patience and help; I believe I now have the problem solved - but just a few points about the "insert" statement you could help me with:

1 could you please explain the syntax of the text portion of the statement:

 ,' "  &  textvariable & " ',

2 I have looked at the MS access help site for information on the "insert" statement; there are several unpaired square brackets in their syntax; are these an alternative to the " & " ?

3 what is the purpose of the final " ; " ?

4 my VBA rejects the " me." keyword - the message is " invalid use of me. keyword"

5 my VBA corrupts date values unless they are syntaxed as text; is this correct?

Ron

 

[CJ_London]

1 could you please explain the syntax of the text portion of the statement:

it is just trying to demonstrate a principle using pseudo code

2 I have looked at the MS access help site for information on the "insert" statement; there are several unpaired square brackets in their syntax; are these an alternative to the " & " ?

NO - square brackets are used when you have spaces or special characters in table and field names - safer not to have these characters, many do and it just increases their development time

4 my VBA rejects the " me." keyword - the message is " invalid use of me. keyword"

The me keyword refers to the form the code resides in - if it is not in a form then you will get this error

5 my VBA corrupts date values unless they are syntaxed as text; is this correct?

No, although it will often work. if you are using text then the format needs to be the US style - i.e. mm/dd/yyyy which if that is where you are is fine, but to ensure access reads it as a date then you surround with the # character so better to use

...., #" & format(myDate,"mm/dd/yyyy") & "#....

Basically you need to understand the difference between text, numbers and dates and how they are referenced in VBA and SQL, together with 'where you are' (e.g. query, form/report, form/report module, standard module) and 'where the value is" (e.g. in the query, on a form, in another table) . Access is generally clever enough to reinterpret text to number or date if appropriate, but not always which is why you should do it 'properly'

It is a very wide subject, I suggest get a book on the subject or use one of the access training sites

 

[ronaldff]

Hello CJ_London

Now the thread is complete; many thanks till the next problem!!!