insert, append from form to table

[miss031]

I have a form bound to a query made up of several tables, and to try and
speed things up, I am using unbound combos (sourced by lookup tables), and
running append queries before exiting the form, after all of the fields are
filled in. This works fine for completely new records, but what do I do if I
would like to change existing ones?

I tried to run update queries from
If Me.[form_cbo_box_name] <> Me.[txt_field_from_main_query] Then

but it won't fire.

I need to find out if the combo has changed, and update it if it has, append
it if it was formerly blank, and ignore it if it is still blank.

I think I need to use a JOIN query, but I don't know how to write it using
fields from a form, or do I use the actual fields from the source tables of
the combos? I just recently enlightened myself to SQL, so I am having a lot
of trouble with syntax. Please help!

 

[Steve Sanford]

If the combo box was bound, you could use the OldValue property and compare
it to the NewValue property. But since the combo box is unbound, you need a
workaround.

I need to find out if the combo has changed, and update it if it has, append
it if it was formerly blank, and ignore it if it is still blank.

So you have 3 conditions:

1) combo box is blank - do nothing
2) combo was blank, but now has value - Append (Insert)
3) combo box value changed - Update


Here is my workaround.

Create a text box on the form. (Later you will set the visible property to
FALSE)
Change the name to "tmp_Value".

Add this code for the combo box Enter event:

'-------------------------------
Private Sub form_cbo_box_name_Enter()
Me.tmp_Value = Me.[form_cbo_box_name]
End Sub
'-------------------------------

This stores the "old value" of the combo box.

This code is in the combo box after update event:

'-------------------------------
Private Sub form_cbo_box_name_AfterUpdate()
Dim curNewValue
Dim curOriginalValue

curOriginalValue = Trim(Me.tmp_Value) & ""
curNewValue = Trim(Me.[form_cbo_box_name]) & ""

' combo box is null
If Len(curNewValue) = 0 Then
'
'clear textbox
Me.tmp_Value = Null

' do nothing so exit
EXIT SUB
Else
' combo box was null but filled now
If Len(curOriginalValue) = 0 Then
'
' your insert code goes here
'
Else
If curOriginalValue <> curNewValue Then
'
' your append code goes here
'
End If
End If
End If

'***** change the control name!!!!
'move to next control
Me.Next_Control_name.SetFocus

'clear textbox
Me.tmp_Value = Null

End Sub
'-------------------------------


What happens is you enter the combo box. The current value is stored in the
text box. You change the combo box. The code compares the text box value to
the combo box value.

For a test, add a message box (msgbox) line to each of the if statements.

When it is working the way you want, set the visible property of the text
box to FALSE.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have a form bound to a query made up of several tables, and to try and
speed things up, I am using unbound combos (sourced by lookup tables), and
running append queries before exiting the form, after all of the fields are
filled in. This works fine for completely new records, but what do I do if I
would like to change existing ones?

I tried to run update queries from
If Me.[form_cbo_box_name] <> Me.[txt_field_from_main_query] Then

but it won't fire.

I need to find out if the combo has changed, and update it if it has, append
it if it was formerly blank, and ignore it if it is still blank.

I think I need to use a JOIN query, but I don't know how to write it using
fields from a form, or do I use the actual fields from the source tables of
the combos? I just recently enlightened myself to SQL, so I am having a lot
of trouble with syntax. Please help!

 

[miss031]

Cool. I was actually thinking along the same lines, but the Trim and Len were
not things I would have thought of using. I'll try it right away. Thanks!

If the combo box was bound, you could use the OldValue property and compare
it to the NewValue property. But since the combo box is unbound, you need a
workaround.

I need to find out if the combo has changed, and update it if it has, append
it if it was formerly blank, and ignore it if it is still blank.

So you have 3 conditions:

1) combo box is blank - do nothing
2) combo was blank, but now has value - Append (Insert)
3) combo box value changed - Update


Here is my workaround.

Create a text box on the form. (Later you will set the visible property to
FALSE)
Change the name to "tmp_Value".

Add this code for the combo box Enter event:

'-------------------------------
Private Sub form_cbo_box_name_Enter()
Me.tmp_Value = Me.[form_cbo_box_name]
End Sub
'-------------------------------

This stores the "old value" of the combo box.

This code is in the combo box after update event:

'-------------------------------
Private Sub form_cbo_box_name_AfterUpdate()
Dim curNewValue
Dim curOriginalValue

curOriginalValue = Trim(Me.tmp_Value) & ""
curNewValue = Trim(Me.[form_cbo_box_name]) & ""

' combo box is null
If Len(curNewValue) = 0 Then
'
'clear textbox
Me.tmp_Value = Null

' do nothing so exit
EXIT SUB
Else
' combo box was null but filled now
If Len(curOriginalValue) = 0 Then
'
' your insert code goes here
'
Else
If curOriginalValue <> curNewValue Then
'
' your append code goes here
'
End If
End If
End If

'***** change the control name!!!!
'move to next control
Me.Next_Control_name.SetFocus

'clear textbox
Me.tmp_Value = Null

End Sub
'-------------------------------


What happens is you enter the combo box. The current value is stored in the
text box. You change the combo box. The code compares the text box value to
the combo box value.

For a test, add a message box (msgbox) line to each of the if statements.

When it is working the way you want, set the visible property of the text
box to FALSE.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have a form bound to a query made up of several tables, and to try and
speed things up, I am using unbound combos (sourced by lookup tables), and
running append queries before exiting the form, after all of the fields are
filled in. This works fine for completely new records, but what do I do if I
would like to change existing ones?

I tried to run update queries from
If Me.[form_cbo_box_name] <> Me.[txt_field_from_main_query] Then

but it won't fire.

I need to find out if the combo has changed, and update it if it has, append
it if it was formerly blank, and ignore it if it is still blank.

I think I need to use a JOIN query, but I don't know how to write it using
fields from a form, or do I use the actual fields from the source tables of
the combos? I just recently enlightened myself to SQL, so I am having a lot
of trouble with syntax. Please help!

 

[Dick Starr]

Hello,

I hope you receive this message.
I am a newbie to Access DB development.
I have a task very similar to the one you said you had already running.
I have a form & sub form, the subform containing (unbound )text boxes that
need to have their values added to the Tips table.
I have an EmployeeID field that would ID the employee in question.

Can you tell me how you tied the unbound textbox fields to the append query?
Will there be any complications related to pulling the DealerID from another
source?

Appreciated any assistance you can provide.

Thanks,

Dick Starr