Assign specific value to table on Click 1

[edge70]

Hi, I have a form for which I can duplicate some values and when it happens I have a messagebox that ask the user if he really wants to have a second entry with the same value and if he clicks yes I want to add the value of my 'modif' variable in a specific cell of that entry and if he clicks no I want to comeback to the form to modify that entry.
By the way, I make this messagebox appear in a 'before update' event

Can someone help me on that ?

Thanks !

 

[Crowley16]

eh, when you say specific cell, what exactly do you mean? Access doesn't really have specific cells, just fields and records, so the best you can do is to put that value in the specific field of a specific record, and you can do that with a insert sql statement...

as for going back to the form, just don't close the form before the msgbox, and then you can use control.setfocus to goto the specific control you want to edit...

--------------------
Procrastinate Now!

 

[edge70]

Your right, I wasn't using the right terms.
Before I add a new record, in my textbox where I enter my data I made a 'before update'event for which a msgbox appears and for yes I want to put the value of my 'modif' variable which is always 'Modification' and for no I want to return to the textbox to modify his value.
I'm checking how the insert sql statement works right now.

I'm quite new working in Access so I have some troubles figuring how to do this

Thanks !

 

[Crowley16]

so, if they click yes, you want to make a new record, insert the values into that record, and the value modification into somewhere on that record as well? (presumably you have a specific field for modification...)

but if they click no, then you just want to go back the the form? (this bit is easy, just do what I said before)

If your form is NOT bound to a record, then it's easy to just use a single sql statement to insert the values:

docmd.runsql("INSERT INTO tblName(field1, field2, ...) VALUES('" & value1 & "', '" & value2 & "', " & ... & "');"

this will make a warning popup, to disable that, put:
docmd.setwarnings false
before the sql
and
docmd.setwarnings true
after the sql

--------------------
Procrastinate Now!

 

[edge70]

The textbox that I tell you is the last of the five fields of this record and actually I use a combobox with the 'New' and 'Modification' values to set the value on my 'ShapeType' field in my 'ShapeList' table.
I wish I could do this without using my combobox because the valus to assign is always relative to the button clicked in my msgbox.

I think that my form is bound to a record (if it means that the values entered in this form are sent to a table).

Here is the part of my msgbox:

If Me.TBShapeNumber.Value = DLookup("[ShapeNumber]", "ShapeListTable", "[ShapeNumber] = " & Me.TBShapeNumber.Value) Then
answer = MsgBox("This shape number already exists, you want to modify this shape?", vbQuestion + vbYesNo)
If answer = vbNo Then Cancel = True
ElseIf answer = vbYes Then Cancel = False
End If

If I understand correctly you tell me to put the insert sql statement instead of the Cancel = False part, right ?

 

[Crowley16]

erm, if your form is bound then all you have to do is to change the value in the textbox/combobox to set the record...

i.e.

if answer = vbyes then
tbxWhatever = "Modify"
else
tbxWhatever = ""
tbxWhatever.setfocus
endif

or something like that...

Since the form is bound, any change to the textbox, will automatically change the record in the table...



--------------------
Procrastinate Now!

 

[edge70]

Good, that's what I was looking for, I'm working on it.

Many thanks !

 

[Crowley16]

be careful with bound forms...

because they are bound forms, they will always need a record to be bound to, so if you've not got a record, then the form will either create a new record or will throw a fit...

both isn't good...

--------------------
Procrastinate Now!