Looks like a challeng to me -combolist is updated but table is not (?)

[avivit]

Hi,
I have a combo-box in A form where the values r taken from a query (select query from a table).
When a value entered is not from list, a "notonlist" event goes to a procedure (following) in which, asks the user if wants to add the item to the list. If yes, then goes to B form, automatically puts there the not-in-list value entered in A form, and then the user manually fills in other details in record. Then the user presses a button that takes closes the form+save, and goes back to A form, where now the combo-box list is updated(!), and when u click the value in the comco-box that was entered before, it opens the combo list and then u can see that it is in the right place in the list.
So far so good.

But - other details (some fields) in A form record, should be taken from the brand new record in B table. Fiedls that matches the value that was entered to the combo-box.

But that is not done, unless I just select the item again from the combo-list.
The error I get is that the value (in comco) is not found in B table. When I open B table, it is there.
I do not understand: If the form is updated, and the combo-box has already taken the new value from it, and also entered it to the list in combo in FORM A, then it means that A form can see B form recent details.
Why can't it see the same record from table?
How can I refresh it, without loosing the new record (the new value actually)?

I hope I am clear.
The code:
'*********
Private Sub BudgetNo_NotInList(NewBudgNo As String, Response As Integer)
'Refreshes the list in the budget no combo-box. Needed in case
'there has been just added a new item to the budgets form
Me!BudgetNo.RowSourceType = "Table/Query"
Me!BudgetNo.RowSource = "SELECT [budgets].[budgetno], [budgets].[ownername] FROM budgets ORDER BY [budgets].[budgetno];"
Me!BudgetNo.Value = NewBudgNo
If Me!BudgetNnNotInList = False Then 'I.e. - if it IS part of the list now

Exit Sub
Else
MsgBox ("requrey shows not in list still&quot
GoTo step2

End If
step2:
Dim Msg, Style, Title, MyString
Msg = "The item is not on the List. Do u want to add it?" ' This is the message on the dialog box.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Gives you Yes and No buttons.
Title = "Not in list item" ' Message Box Title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' If User wants to add a new item, then
DoCmd.OpenForm ("frmbudgets&quot 'open the right form,
DoCmd.GoToRecord , , acNewRec 'go to new rec
'DoCmd.GoToControl (budgetNo)
Forms!frmbudgets!BudgetNo.Value = NewBudgNo 'and set the value of budgetNo as was entered to the current form
Else ' If No.
MsgBox ("No&quot
End If

End Sub
'**************


Thanks

 

[FancyPrairie]

Try to refresh or requery the combobox. See what that does.

 

[avivit]

How do I refresh the combo box?

 

[Nikita6003]

Me!BudgetNo.Requery

Cheers
Nikki

 

[avivit]

Thanks
So refresh means requery. I thought u mean there is a different thing from requery and I wondered what it was.

I will try and let know what happened.

Thanks

 

[Nikita6003]

Well, the refresh is meant for the form:

Me.Refresh

This'll pick up on changes made which affected the form

The Requery is for the recordset of your combobox

Cheers
Nikki

 

[avivit]

Ok, the results:

1. Me!BudgetNo.Requery
gives me an error, saying that I must save the field before requerying.

2. As for refresh. I get 2 kind of errors, depending on where I put this line.

a. "The text isn't an item in the list"
b. Something with not abling to find the right matching fields in table "budgets", which is my problem in the first place.

 

[avivit]

What if I just want to select a certain item from the list?
I know the value I need from the combo list, and want to select it through vba, and not manually.
How do I do that?

Thanks

 

[lameid]

Read the help topic on the NotInList event and note the correct uses of the Response variable. You used it to store a msgbox result and need to correct that.

 

[avivit]

What harm can it cause?
This part does work.

 

[lameid]

Private Sub BudgetNo_NotInList(NewData As String, Response As Integer)
Dim Msg, Style, Title, MyString
Msg = "The item is not on the List. Do u want to add it?" ' This is the message on the dialog box.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Gives you Yes and No buttons.
Title = "Not in list item" ' Message Box Title.
If MsgBox(Msg, Style, Title) = vbYes Then ' If User wants to add a new item, then
'You really ought to append the value with an sql statement or recordset instead of this mess
DoCmd.OpenForm ("frmbudgets&quot 'open the right form,
DoCmd.GoToRecord , , acNewRec 'go to new rec
'DoCmd.GoToControl (budgetNo)
Forms!frmbudgets!BudgetNo.Value = NewData 'and set the value of budgetNo as was entered to the current form
'You probably need to save the value in the form or use the query or recordset methods to add a value.
Response = acDataErrAdded
Else ' If No.
Response = acDataErrContinue
End If

End Sub

 

[avivit]

" 'You probably need to save the value in the form or use the query or recordset methods to add a value."

That is the big q. How?
Re-querying won't work, and asving won't either since there is a new item that is not in list yet.

When I do it my wayMe!BudgetNo.RowSourceType = "Table/Query"
Me!BudgetNo.RowSource = "SELECT [budgets].[budgetno], [budgets].[ownername] FROM budgets ORDER BY [budgets].[budgetno];"
Me!BudgetNo.Value = NewBudgNo)

then the value as the user typed is there, and also the list in combo is updated(!), but something else isn't, and that's why I get error of not finding the matching-corresponding fields to the new record.

I'll be happy to know HOW to "save the form" without loosing data and the auto-number of the new record.

Anyway, I'll try the 'Response = acDataErrAdded' stuff.

Thanks