First Function - One Problem!

[sara]

I've gotten my first function to work - to build a sql string to load a
list box. I call the function whenever I want the list box loaded

1. When a customer is chosen - opens the frmCust and shows the
lstItems with all the customer's items (works great)

2. When an item is deleted - goes back to the frmCust and shows the
lstItems and the deleted item is gone (works fine)

3. When an item is changed - goes back to the frmCust and show the
changed info (if it is one of the listbox fields) - Works

4. When an item is added - goes back to the frmCust and should show
the new item in the list (NOT WORKING)

Change uses the exact same code as add. Delete uses a "delete item"
button.

I step through the code and look at the tblCustomerItem s at each step,
and see that the item is NOT in the table until after the function has
excecuted. I tried "DoCmd.RunComma nd acCmdsave" and that didn't work.

Gotta be something easy - I'm missing. Thanks! Sara

Code:

Private Sub cmdCloseGarment _Click()
On Error GoTo Err_cmdCloseGar ment_Click

Dim CustID As Long
CustID = Me.txtCustID
Dim intfcnReturn As Integer

' User enters (or updates) the item and presses CLOSE when done

' Tell the form NOT to cancel after the call to the Update event
' Forcing the Before Update to make sure if there is damage or soil,
there is
' detail on the problem entered

' Run the Before_Update event
' If the user is missing details, this sub will set StopMe to send the
user back to the
' form to insert details (required if damage or soiled item)

Form_BeforeUpda te (False)

' Determine if the user should be stopped - if the user checked damage
or soil, but
' didn't input details
If StopMe = True Then
Forms!frmCust.l stItems = CustID
Forms!frmCust!l stItems.Requery
Else

' Try to force the record save
' DoCmd.RunComman d acCmdSave

' Update has been captured on the table and the audit; go back to the
cust form with
' items added/update

' Set the value for the function

' Call the Function to repopulate the list of items for the customer
' get the return value from the function
If fcnFillGarmentL ist(Me.txtCustI D) = False Then
MsgBox "Function Fill Garment List Failed. Call Sara", ,
"Function Fail"
Exit Sub
End If

' MsgBox fcnFillGarmentL ist(Me.txtCustI D)

' Close the garment form and return to the customer form, with updated
list
DoCmd.Close acForm, Me.Name
End If
' Reason: Want to requery the items list, not customers if updating
item only

Exit_cmdCloseGa rment_Click:
Exit Sub

Err_cmdCloseGar ment_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdCloseGa rment_Click
End Sub

[Phil Stanton]

Hi Sara

Not clear whether you are using a list box to show your garments or a
subform. The DB I sent you worked with a subform in which case it is easy to
add, edit or delete garments. No code needed.
What are you trying to achieve? Damaged or soiled should just be a checkbox
on the subform and held in the JnCustomerGarme nt table

Phil
"sara" <sa*******@yaho o.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com.. .

I've gotten my first function to work - to build a sql string to load a
list box. I call the function whenever I want the list box loaded

1. When a customer is chosen - opens the frmCust and shows the
lstItems with all the customer's items (works great)

2. When an item is deleted - goes back to the frmCust and shows the
lstItems and the deleted item is gone (works fine)

3. When an item is changed - goes back to the frmCust and show the
changed info (if it is one of the listbox fields) - Works

4. When an item is added - goes back to the frmCust and should show
the new item in the list (NOT WORKING)

Change uses the exact same code as add. Delete uses a "delete item"
button.

I step through the code and look at the tblCustomerItem s at each step,
and see that the item is NOT in the table until after the function has
excecuted. I tried "DoCmd.RunComma nd acCmdsave" and that didn't work.

Gotta be something easy - I'm missing. Thanks! Sara

Code:

Private Sub cmdCloseGarment _Click()
On Error GoTo Err_cmdCloseGar ment_Click

Dim CustID As Long
CustID = Me.txtCustID
Dim intfcnReturn As Integer

' User enters (or updates) the item and presses CLOSE when done

' Tell the form NOT to cancel after the call to the Update event
' Forcing the Before Update to make sure if there is damage or soil,
there is
' detail on the problem entered

' Run the Before_Update event
' If the user is missing details, this sub will set StopMe to send the
user back to the
' form to insert details (required if damage or soiled item)

Form_BeforeUpda te (False)

' Determine if the user should be stopped - if the user checked damage
or soil, but
' didn't input details
If StopMe = True Then
Forms!frmCust.l stItems = CustID
Forms!frmCust!l stItems.Requery
Else

' Try to force the record save
' DoCmd.RunComman d acCmdSave

' Update has been captured on the table and the audit; go back to the
cust form with
' items added/update

' Set the value for the function

' Call the Function to repopulate the list of items for the customer
' get the return value from the function
If fcnFillGarmentL ist(Me.txtCustI D) = False Then
MsgBox "Function Fill Garment List Failed. Call Sara", ,
"Function Fail"
Exit Sub
End If

' MsgBox fcnFillGarmentL ist(Me.txtCustI D)

' Close the garment form and return to the customer form, with updated
list
DoCmd.Close acForm, Me.Name
End If
' Reason: Want to requery the items list, not customers if updating
item only

Exit_cmdCloseGa rment_Click:
Exit Sub

Err_cmdCloseGar ment_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdCloseGa rment_Click
End Sub

[sara]

I did use the database you sent for learning, but this is a list box
(when I do "orders" I am doing a subform).

I need to capture all edits as the user is so new and nervous. In this
case, I am trying to show the garment that was just added or changed in
the list box when the user finishes the ad or update (or NOT show it in
the case of "delete" where I'm just changing the status to "I"
(inactive). For all that, I need code, hence the function and I used
Allen Browne's audit Trail code (which works great, by the way).

Damage and Soil is not a problem; it's getting the new record to show
in the list box that isn't happening. Changes show; deleted items
disappear.

Does this help explain the problem?

Sara

[Phil Stanton]

Not really.

Do you have a microphone and headset and messenger, in which case we can
talk the problem through

Phil
"sara" <sa*******@yaho o.com> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.com...

I did use the database you sent for learning, but this is a list box
(when I do "orders" I am doing a subform).

I need to capture all edits as the user is so new and nervous. In this
case, I am trying to show the garment that was just added or changed in
the list box when the user finishes the ad or update (or NOT show it in
the case of "delete" where I'm just changing the status to "I"
(inactive). For all that, I need code, hence the function and I used
Allen Browne's audit Trail code (which works great, by the way).

Damage and Soil is not a problem; it's getting the new record to show
in the list box that isn't happening. Changes show; deleted items
disappear.

Does this help explain the problem?

Sara

[sara]

You are amazing! I actually put in acCmdSaveRecord and it worked. I
looked through tons of stuff on save and apparently the acCmdSave was
saving the FORM, and I needed to save the record. I fixed that, and
have a new problem.

Should I explain it here or do a new post? (It relates to running a
function to validate an email address, and wanting to put the cursor
back in the email address field if the update fails).
BTW: I think my PC has a microphone, but I don't have a headset and I
don't know what a messenger is. The IT guy at work isn't in today, but
I could ask him - for future (I am certain I will have more
problems/questions before this is done! This db is supposed to help me
learn how to develop a simple app, really Understand what I'm doing,
and have a model for apps I can build at work).

Sara