Transfering data from form to table by button

[pawelwolanczuk]

Hello there You all!



I've got a problem with some with creating button in the form, which would save data in my database's table. The thing is there are multiple of functions written by me there and (?) it cannot simply be bound to the table. Let's say one text include such a formula:

=IIf([klient]=TRUE;[Imię];DLookUp("[Imię]";"
[Lista]";"[numer rodzinny] = '" & [numer rodzinny] & "' AND [M/S] = 'M' "))

where [klient] is TRUE/FALSE choice list, [imie], [numer rodzinny] are text areas and [M/S] is list box.
[Lista] is name of the table.
All of them are included in this form and they input data to the table automatically. Except from those where I input my formula. I've decided to make a function button to do this and... I failed.

Button I've created is has VBA code like this:

Private Sub AktualizujFunkcje_Click()


Dim db As Database
Dim rs As DAO.Recordset


Set dbVideoCollection = CurrentDb
Set rs = db.OpenRecordset("Lista")


rs.AddNew
rs.Update
rs("imienazwiskoopiekuna").Value = Me.Tekst488
End Sub

AktualizujFunkcje - name of button
Lista - name of table
imienazwiskoopiekuna - name of column in "Lista" table
Tekst488 - name of text area, where my formula is set.

all other is connected to some tutorials/examples I've seen on the Net. Not sure if they work properly.

Could any of You, please, help me with that? Where in button's code there is an error ? Is there any other way to accomplish my aim?

Thanks in advance,
Paweł

[pbaldy]

Not sure that a bound form couldn't work, but one problem with your code is the order. You want:

rs.AddNew
line(s) setting values here
rs.Update

[Dal Jeanis]

You are attempting to add a new record to the table, right?

The following code is taken verbatim from this Microsoft page - http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Function AddName(rstTemp As Recordset, _
   strFirst As String, strLast As String)

   ' Adds a new record to a Recordset using the data passed 
   ' by the calling procedure. The new record is then made
   ' the current record.
   With rstTemp
      .AddNew
      !FirstName = strFirst
      !LastName = strLast
      .Update
      .Bookmark = .LastModified
   End With
End Function

This code indicates that the .Update should occur **after** the field values are changed.

Also, you can use the !xxx coding convention rather than the rs("xxx") convention if you want. I always prefer that, because the convention with quotes in it makes me nervous about whether an inefficient lookup on the collection might be being used, rather than a pre-linked reference. The quotes version is probably just as efficient, but it doesn't **feel** like it.

[pawelwolanczuk]

Hey Guys! Thanks for quick reply! I'm trying to either add record to the table or update one which already exists. Shall I use "edit" instead of addnew then? To be honest, I'd like my function would do both of them.


Firstly, I've tried 1-st tip (just changing the sequence). It didn't work :-(

And I got the question about the other answer:

I assume that strFirst and strLast are text areas (as variable string). Could You, please, answer my three questions next? I mean:

1. line .Bookmark = .LastModified is to making new record current one, right ?
2. rstTemp is a name of table/recordset, right ? In my case (my table's name is "Lista") it would be: rstLista, wouldn't it? I need to change it in two places in that code:
a) in function's arguments
b) after "with"
is that correct?
3. Can I input this function in button's VBA code? I mean:

It would like like this:

Private Sub AktualizujFunkcje_Click()


Function AddNew(rstLista As Recordset, _
strFirst As String)


With rstLista
.AddNew
!imienazwiskoopiekuna = Me.Tekst417
.Update
.Bookmark = .LastModified
End With
End Function
End Sub

Still doesn't work, though :-( Could You help me ? Many thanks for the next answers, Mates. I'm really in Your debt.

[pbaldy]

Edit will edit an existing record, AddNew will add a new record. If this doesn't work:

rs.AddNew
rs!imienazwiskoopiekuna = Me.Tekst488
rs.Update

Make sure the value being added is appropriate and that there aren't other required fields that aren't being populated. Can you post the db here?

[pawelwolanczuk]

I cannot upload my database as it contains personal data of my clients. I can upload the schema, though.


As it's just v. 1.0, please don't laugh at me cause of GUI. I know it's not really cool.


Here's the thing: The Lista_formularz is devided into some parts:
a) Dane podstawowe (area texts)
b) Adres (area texts)
c) Umowa (formulas)


All area texts are connected to Lista table. I'd like to accomplish transfering data from formulas (eg. Imię opiekuna) into Lista table (let's say) Imię Opiekuna column.


As far as I do have some problems with names containing space in those, I've made new column imienazwiskoopiekuna. It will not exist in my final database. I don't mind changing my columns' names into one word, without space between them two, though.


Back to the form:


I'm not sure if function should be inside sub. I thought not. I've uploaded what I've done:

http://speedy.sh/dafWw/lista.accdb

[pbaldy]

I'm not comfortable downloading from unknown sites. If you do a compact/repair and then zip, you should be able to attach it here.

[pawelwolanczuk]

Not sure if I made it correctly, but let's give it a shot:

lista.zip

Oh, yeah. Attachment worked, I guess.

[pbaldy]

A function can not be inside a sub. I'd start without the function, to learn how things are working. I'm getting an error that makes me think something is corrupt, so I'll have to come back to this later. I was trying this as a simple test:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Lista")

rs.AddNew
rs("imienazwiskoopiekuna").Value = "test"
rs.Update

Set db = Nothing
Set rs = Nothing

[pawelwolanczuk]

@pbaldy
Thanks for Your time, my Friend!

I got sth I've created (and it works somehow). Could You look at it, please? I got one more issue about that. I've changed names (deleted spaces and special chars like "/") both in the form and table. To make other things even smoother I made all of areas (texts, choices and formulas) transfering to the table. It may seem very improfessional for You and others, but I'm just a newbie about that, so I decided to do this without functions (that was disaster when I tried anything).

Here is my code. It seems to work somehow.

Private Sub AktualizujFunkcje_Click()
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Lista")
With rst
rst.AddNew


!Imie_Opiekuna = Me![ImieOpiekuna]
!Nazwisko_Opiekuna = Me![NazwiskoOpiekuna]
!SN_Dowodu_Opiekuna = Me![SNDowoduOpiekuna]
!PESEL_opiekuna = Me![PESELopiekuna]
!Kod_Pocztowy_Opiekuna = Me![KodPocztowyOpiekuna]
!Miasto_Opiekuna = Me![MiastoOpiekuna]
!Ulica_Opiekuna = Me![UlicaOpiekuna]
!Numer_Lokalu_Opiekuna = Me![NumerLokaluOpiekuna]


!imie = Me![imie]
!Nazwisko = Me![Nazwisko]
!tel = Me![tel]
!tel2 = Me![tel2]
!email = Me![email]
!rok_urodzenia = Me![rok_urodzenia]
!SN_dowodu = Me![SN_dowodu]
!numer_umowy = Me![numer_umowy]
!numer_rodzinny = Me![numer_rodzinny]
!okres_wypowiedzenia = Me![okres_wypowiedzenia]
!MS = Me![MS]
!numer_grupy1 = Me![numer_grupy1]
!numer_grupy2 = Me![numer_grupy2]
!numer_grupy3 = Me![numer_grupy3]
!numer_grupy4 = Me![numer_grupy4]


!LP = Me![LP]
!kontakt = Me![kontakt]
!poziom = Me![poziom]


!zainteresowany = Me![zainteresowany]
!uczen = Me![uczen]
!klient = Me![klient]


!niem = Me![niem]
!ang = Me![ang]




!miasto = Me![miasto]
!ulica = Me![ulica]
!numer_lokalu = Me![numer_lokalu]
!kod_pocztowy = Me![kod_pocztowy]
!notatka = Me![notatka]


rst.Update
.Bookmark = .LastModified
.Close
End With
End Sub

I'm not really sure what are those:

Dim db As DAO.Database
Dim rs As DAO.Recordset

and whether I need You pasted at the end:

Set db = Nothing
Set rs = Nothing

Could You please explain me (if it's easy to explain) what are those for ?
Frankly speaking, my true problem now is: what if any of my clients change address, get married (changes in last name) or just I make a mistake while inputing data.
The button can only add new record, not edit one.

I've tried to make another button with almost same code, except from: rst.AddNew => rst.Edit. That, of course, does not work as I would like it to.

[pbaldy]

The first two:

http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx

The second two are cleaning up. There is a rule of thumb that any variable set with the word "Set" should be set to nothing to clear memory.

The Edit wouldn't work because of how you open the recordset. You opened it on the entire table, so at the point that code runs it would edit the first record in the table. I would open the recordset on an SQL statement that retrieved the exact record. That said, if you're trying to save values that are calculated, have you considered the second method here (presuming saving them is appropriate at all):

http://allenbrowne.com/casu-14.html

[pawelwolanczuk]

Thanks for the links and explanation!

I think I don't need queries here. I got only one calculated result in table. All of my formulas are in the form. That is, I'd like it to change dynamically, but it doesn't in 100%.


Two examples here (all is done while form is concerned, neither table or query):
a) When I input 'TRUE' value in "klient", it should reput data from text areas like: first name, last name, address into others text areas which I need for contracts.
b) If "klient" is 'FALSE', but "uczen" is 'TRUE', text areas for contracts should "look for" data from other, connected by [MS] and [numer_rodzinny] and having value 'TRUE' in "klient".


That "looking for" is my formula(DLookUp). It works quite well, but not perfectly. I need to save one record before updating it so that it could search others for data. I could get by that. At least for some time.


As I said before, I'm quite newbie here. I use only two queries and those are just to make my life easier (looking for connected records). I cannot even imagine how to use queries for my formulas, frankly speaking.




You were right about updating records. I've managed to make button which saves data to one record only. Not sure how to do it right so far.

[pawelwolanczuk]

@pbadly

I've done sth but still I got some problems. Could You, please take a look for the code for me?

This is new formula (each formula is bit different - they differ from each other by name of column, but this is an example):

=IIf([klient]=True;
    IIf(IsNull([imie]);
        'None';
        [imie]
    );
    IIf(IsNull(DLookUp("[imie]";"
[Lista]";"[numer_rodzinny] =  '" & [numer_rodzinny] & "' AND [MS] ='M' "));
        'None';
        DLookUp("[imie]";"
[Lista]";"[numer_rodzinny] =  '" & [numer_rodzinny] & "' AND [MS] ='M' ")
        )
)

And this is button's code:

Private Sub Aktualizuj_Click()


DoCmd.RunSQL "Update Lista SET Imie_Opiekuna = " & Me!ImieOpiekuna & "  WHERE Identyfikator = " & Me!ID & ""


End Sub

I've tried on numbers first (I changed: 'NONE' into 0 and input data contained only numbers). It worked. I'm glad it did, but I have lots of text areas containing letters. Could You, please, give me a tip how to change my code so that it could work ?

Edit:
If I put " & Me!ImieOpiekuna & " into '' everything is okey. My DB works fine now. Thread can be close now.
@pbaldy, Thank You, very much for whole help, my Friend!

[pbaldy]

Glad you got it working!