writing userform input to database

[pclaesen]

Hi,

I wrote an accounting program in excel VBA, linked with products (sale and buy etc.).
The problem I currently encounter is that I have too many products to store in Excel spreadsheets, so I started re-writing it in in access.

I already searched the intrnet for some hour, but I'm still stuck at some very basic stuff. I have a userform which needs to write a new record to the database.

Layout of the database:
Table name = Klanten
Field 1 of Klanten is 'ID'
Field 2 of Klanten is 'Klantnaam'

The current code I have is:

Please Login or Register  to view this content.

Could anyone please shine some light on this, as it's the very basic thing I need to know before I can continue.
I prefer using access for everything instead of using excel linked with access.

Thanks!

[cytop]

A couple of issues:

• Klantnaam is declared as 'Integer' but you are assigning the value of a textbox to it. Maybe Access will coerce the textbox contents to an Integer but I doubt it. The line 'klantnaam = txtKlantnaam.Text' will probably throw an error unless Klantnaam is declared as a string
• You cannot access the .Text property of a control in Access unless that control has focus. Use the .Value property if you do not want to set focus to the control first.
• rst & db are Objects, to destroy them you must use the Set keyword.

Your original code updated:

Please Login or Register  to view this content.

Although, to make things easier Access can handle all this using the following:

Please Login or Register  to view this content.

2 extra fields were added as examples of inserting different data types.


sql.jpg

[pclaesen]

Hi,

Appreciate the fast answer.
I tried both methods, but my table Klanten isn't updating, no records are being written to it. Maybe I did something wrong in the userform I'm not sure, I attached it.
Could you please have a look at it?

[cytop]

I can't stand those embedded macros in Access, and that is what was called on the button click. Change it to call an 'Event Procedure' (Click the Down Arrow to the right of the Click event for the button in the Properties dialog) then click the Ellipses (...) to the right of that.

Doing that will open an event stub for the click event. The full code, including the correct field names, is below.

FFS - this firewall is a PITA. Code in attachment - right click the button in design View, select 'Build Event' to view the code.