Inser into table with button

[dntel123]

Can someone help me with the correct code for inserting a value into a specific row or a column in the Database:

So, user clicks button, prompt will say enter a value - enter value - and that value is inserted into the right box within the table.

This is what I have so far:

Dim MYDB As Database
Dim SOURCE As Recordset


'initialise error handler

On Error GoTo ErrHandler:

'set variables
Set MYDB = CurrentDb()
'create a recordset
Set SOURCE = MYDB.OpenRecordset("Tbl Module_Delivery")

'move to the first record
SOURCE.MoveFirst

'initialise add new record
SOURCE.AddNew
'input values using Input Box to get input
SOURCE![Pass Mark] = InputBox("Please Enter the Required Value")
'update the database with these values
SOURCE.Update

'notify that data was successfully added to database
MsgBox "Data Added!"

ONWARD:

SOURCE.MoveNext

'error handler: exits current action
ErrHandler:

I know this should work, but my Table has 5 rows, and I want to be able to insert a value into Pass, Merit or Distinction for any of them like so:

Thanks!

 

[Scooterbug]

You said that you want to insert a value into one of three fields...but the

.addnew

is actually creating a new record.

If you are looking to insert or edit a field, you first will need to find the particular record, then use .edit, then specify what fields need to be updated, then tell it to actually update the record with .update

Is there any reason why you aren't building a form, finding the record via a search or filter and changing it that way?

 

[dntel123]

Well I am building a form yes.

How would I do what your saying there?

Thanks for the reply btw

 

[Scooterbug]

I did up a quick db to show you...I do better with examples than I do with description

Basically, I built a form based off of the table. I then added a combo box with a list of all the Modules. Once a module is selected, it will populate the form with the record. Just simply edit the record from there.

 

[dntel123]

I'm not sure if that is showing correctly (office 2007 im on) it just has on nav pane Query1 and thats it no form?

that how its spose to be? when u run query just asks me to enter parameters

Cheers!

 

[Scooterbug]

Go to the Forms Panel....there should be a form called Form1. Open that up. The query is what determines the data that is being displayed on the form. The parameter is based off the combo box on the form.

 

[dntel123]

Oh I gotcha

Yer thats perfect mate actually thanks very much, makes me understand it a lot better with an example thanks a lot!

 

[dntel123]

Is there also a way to; once edited/updates the values of pass merit etc, to date stamp the change?

 

[Scooterbug]

Yup...in the after update event of the field that you want to change, you can simply put the following code:

me.[fieldName] = date()

where fieldname is the name of the field that will store the date that the value was changed. If you also want to record the time along with the date, change date() to Now()

 

[dntel123]

Re: Insert into table with button

Ok this is driving me mad now seeing as i think i have it right but it just doesnt work, the combo box populates with the module code but my text boxes that are meant to show the other information just dnt respond even though they have a control source.

Think you could take a look, I named them form1 and Query 1.

It should populate shouldnt it? if u have a text box with a control source linking to a table?

 

[Scooterbug]

I dont have access 2007, but I'm pretty sure that I know what the issue is. Make sure that the combo box has both the Module name and ModuleID in the row source. The ID should be the first column and the name should be the second column. The bound column for the combo box should be 1. You can set it so that the ID number doesn't show by setting the column widths in the combo box properties to 0";1". Make sure that the number of columns in the properties is set to 2.

In the query, the criteria should be set under the ModuleID field. It should read:
[forms]![form1]![ComboBoxName]. You should also put the following code in the After Update event of the combo box:

me.requery

That should do it.

 

[dntel123]

Ok, give tht a try now.

My row source for combo box is :

SELECT tblModuleDelivery.Module_DeliveryID, tblModuleDelivery.ModuleID FROM tblModuleDelivery;

Seems right to me..

This is my Query:

I'll also attached (in access 2000 format) just incase u wanna look

Thanks for ur help its appreciated, quite frustrating as its supposed to be simple lol

 

[Scooterbug]

The problem is the row source for the combo box....you have this:

SELECT tblModuleDelivery.Module_DeliveryID, tblModuleDelivery.ModuleID FROM tblModuleDelivery;

When it should be:

SELECT tblModuleDelivery.[Module_Delivery ID], tblModuleDelivery.ModuleID FROM tblModuleDelivery;

in the source you have currently, you have Module_Delivery ID spelled wrong...you need to have the space before the ID...since that is what you named it Make sure you put it in brackets...else you will get an error. This is one reason why you shouldn't use spaces when naming stuff

 

[dntel123]

Gah, damn spelling mistakes

Ive now done that and date stamped it, thanks for all your help, gave you plus rep

Liam

 

[Scooterbug]

Glad it worked out!