Insert data into another form

[delaikhi]

Here is a problem:
I have a combo box Customer_Name on Order Form; and I want it open the Customer Form when an user insert a new customer to input all data. This seems simple at first, but problems are these:
1- whenever an user opens Customer Form on a new customer, the user would have to make the input once again (at least retype the customer's name on the field).
2- since the Customer_Name and other fields are required in the table, the Order_form would not allow the user to close it unless all field are filled properly. Dilemma is you have to fill in the Customer_Name field before the table refresh all records and display it in the combo box on Order Form :banghead:
Is there any way to go around these problems? Thanks

 

[madefemere]

Hello,
I presume you use the NotInList event of your combox ?
You can use the NewData argument to assign the name of the new customer after opening the form as below :

Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
stDocName = "F_Customer"
Response = acDataErrContinue
AnswerQ = MsgBox("This Customer does not exist !" & vbCrLf _
& " Do you want to add it ? ", vbYesNo)
If AnswerQ = vbNo Then
    Me.CboCustomer = ""
    Exit Sub
Else
    DoCmd.OpenForm stDocName
    Forms!F_Customer.NameCustomer = NewData
    Me.CboCustomer = ""
End If
End Sub

For the second point, there is no solution if values on these fields are compulsory. May be you can reduce the number of fields with mandatory values? Or assign default values?

 

[delaikhi]

Hello,
I presume you use the NotInList event of your combox ?
You can use the NewData argument to assign the name of the new customer after opening the form as below :

Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
stDocName = "F_Customer"
Response = acDataErrContinue
AnswerQ = MsgBox("This Customer does not exist !" & vbCrLf _
& " Do you want to add it ? ", vbYesNo)
If AnswerQ = vbNo Then
    Me.CboCustomer = ""
    Exit Sub
Else
    DoCmd.OpenForm stDocName
    Forms!F_Customer.NameCustomer = NewData
    Me.CboCustomer = ""
End If
End Sub

Thanks, I did insert this Code in to the after update section but got an error message: Run-time error -2147352567 (80020009) The value you entered isn't valid for this field! What is it?

 

[madefemere]

Insert at NotInList event of your combobox as I said

 

[delaikhi]

Thanks, I got it

 

[delaikhi]

Guys, I did every thing as posted in previous posts but stucked with a problem:
After puting a new entry into Customer, I close the Customer form and continue with Order form, which still open at the time. Problem is I have to close the Order form and then reopen it so that the data of the new Customer can be displayed in the combo Box Customer; besides, if I close the Order form, the Ms Access keeps telling me it cannot find the Customer Name in the combo box. I know there should be some sorts of procedures to refresh data in the Order form but cannot understand it. Please tell me how to overcome this issue. Thanks

 

[IndigoStickman]

Is the customer order form that is still open visible or not? It's just that I have a similar thing running in a database of mine, but wouldn't want to misadvise you as I'm still a relative beginner when it comes to Access programming.

 

[delaikhi]

Indigo, It still opens in the background. Actually, I put the above codes into OnNotInList property of the Customer control. Things work find with open Customer form, but when I refocus to the Order Form, there is problem. Ms Access keep telling me that it cannot find "Product_Type" (another required field) althought I type in the new customer name

 

[IndigoStickman]

Hmm,

Will a simple Me.refresh on something like the "got focus" or "on activate" event not fix it? Or am I looking around the issue.

 

[delaikhi]

No, it doesnt work. the Error message I got is : run_time error -214352567 (80020009)

 

[IndigoStickman]

I've done a quick sweep of the forums and it seems your runtime error:

run_time error -214352567 (80020009)

Is that the recordset is not updateable. I'm sure that means that you have locks in place to prevent additions etc in the source of the form. I'd look at the controls if it was me to see if I've locked them in error. I had this when trying to populate from an external linked table, and locked the controls thinking this would just prevent anyone trying to edit the info, but it actually stopped the fields populating too as the fields lock everything.


Again, as a relative beginner myself I may be dancing arund a solution, but I'm here to throw my hat in the ring until someone more experienced may come along and fix it in a flash

 

[IndigoStickman]

I'm also racking my brains to find an alternative method to get that field to populate for you using my working knowledge.

 

[pr2-eugin]

Back to the drawing board, how is the new customer information collected? In other words.. How is the form opened if a new customer is to be added?

 

[delaikhi]

Okay, here is the situation:
On the form Order, I have a combo box called Customer Name (look up from Customer Name in tblCustomers). So that I put the codes as indicated above in NotInlist property box of the combo box Customer Name (I want it to open the Customer form whenever a new Customer turns up).
Now I got 2 problems:
1- It open up the Customer form okay, but when the user come back to the Order, it DOES NOT update name of the new Customer in the combo box. The user has to close and reopen the form so that the info can pe put in.
2- On the Customer form I have a control Custome_Code, I want to assign a number (incremental by 1 from the last entry) to each new Customer, so I put this code in the Customer form:
Private Sub Customer_Name_BeforeUpdate(Cancel As Integer)
Me.Customer_Code.DefaultValue = Me.Customer_Code + 1
End Sub
And I got an error: Run time error "94" Invalid Use of Null
Not sure when I was wrong. Thanks