Full Version: converting form to subform problem
UtterAccess Forums > Microsoft® Access > Access Forms
killerwhale65
hi,
I used the following search code on a form, and it worked fine.
CODE
Dim strStudentRef, strSearch As String
    
'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("SKU")
    DoCmd.FindRecord Me!txtSearch
        
    SKU.SetFocus
    strStudentRef = SKU.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
'If matching record found:
    If strStudentRef = strSearch Then
        [aantal in stock].SetFocus
        
'If value not found:
    Else
            Recordset.AddNew
            SKU = txtSearch
            Manufacturer.SetFocus
    End If

I have made this form a subform now but it doesnt work anymore. What should i change?
thanks!
Peter46
Where is this code held?
killerwhale65
in the afterupdate of a control on that subform
killerwhale65
anyone please?
Jack Cowley
If you are now using a subform to display your data you can use the Master/Child links as a way to filter the data in the subform. Are you already filtering the data in the subform via the Master/Child links? If so, how many items are you filtering on now? What do you want to filter on if you are not already filtering the subforms using the Master/Child links?
ack
killerwhale65
hi,
Please no Chinese, but plain English ;-)
I just want the above code (which works in a standalone form) to be changed to work in a subform.
For example the line
CODE
DoCmd.GoToControl ("SKU")

gives me an error.
I tried making it
CODE
DoCmd.GoToControl ("forms!frmMain!frmStock_Addnew.form!SKU")

and
CODE
DoCmd.GoToControl ("forms!frmMain!frmStock_Addnew.SKU")

but it still errors.
Jack Cowley
You do not say where you are trying to run this code from so I am going to guess the main form:
e.SubformControlName.SetFocus
Me.SKU.SetFocus
Change the object names above as needed... This artilcle will help you with the required syntax for referring to controls on form, subforms and subsubforms.
hth,
Jack
killerwhale65
i already said its in the afterupdate of a control on that subform.
Is docmd.gotocontrol the same as setfocus?
Jack Cowley
Why don't your try SetFocus and/or read the article I suggested?
killerwhale65
because i dont know if setfocus will do the same as gotocontrol.
If i need to test every line and in the end it seems not to work because i changed the original code with setfocus then i will lose a lot of time.
killerwhale65
DoCmd.FindRecord Me!txtSearch
errors:
Can't use Find or Replace now."
Jack Cowley
If you are tying to find a record within the recordset the form is based on why not use a combo box? If you create a combo box on the form using the Wizard and select the 3rd item on the Wizards first screen, "Find a record..." then selecting a record from the combo will show that record on your form.
killerwhale65
Djeez, why didnt i come up with that myself? LOL
Thanks for the suggestion, it works fine.
However i need some additional tweaking of the code.
I want an IF statement that checks if the value i just enteren in the combobox already exists (in the afterupdate of the combo).
I have been trying:
CODE
If rs.EOF = True Then
        Recordset.AddNew
'        SKU = txtSearch
        Manufacturer.SetFocus
    End If

But without luck. Where am i wrong?
Jack Cowley
If you create the combo box using the Wizard it should only show records that are in the forms recordset. You should not need to check to see if the record selected is in the recordset as it won't be in the list box if it is not.
If you want to enter a NEW item then use the Not In List event of the combo box to do what you need. This article has all the answers.
hth,
Jack
PS. I am out of the UA building until 2pm today...
killerwhale65
thanks i will try that one.
Jack Cowley
You are welcome and good luck with this... I'm outta here!
ack
killerwhale65
i have this
<
But the code is not executed when i enter a value that is not in the list.
Jack Cowley
Your code is incomplete. If you will look at Method 1 in the artilce I sent you that should get you started. I do not know much data your users will enter for a new record in the combo box so use the method in the article that is best for you.
th,
Jack
killerwhale65
hi, i tried but it simply does not seem to execute the code:
CODE
Private Sub Combo20_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
    
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblStock", dbOpenDynaset)
      
            
        ' Create a new record.
        rs.AddNew
        
        rs![SKU] = NewData
        ' Save the record.
        rs.Update
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
'Recordset.AddNew
'SKU = NewData
Manufacturer.SetFocus
End Sub
Jack Cowley
Add error handling to your code and see if you get any errors. If you do that will help you track down the problem. Also, do you have the Microsoft DAO 3.6 Object Library checked in References?
ack
killerwhale65
DAO is checked.
o errors appear with this code:
CODE
Private Sub Combo20_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error GoTo Err_ICAO_NotInList
    
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblStock", dbOpenDynaset)
      
            
        ' Create a new record.
        rs.AddNew
        
        rs![SKU] = NewData
        ' Save the record.
        rs.Update
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
Exit_ICAO_NotInList:
       Exit Sub
Err_ICAO_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue
'Recordset.AddNew
'SKU = NewData
Manufacturer.SetFocus
End Sub

Its like it does not execute it at all. Or it thinks that the value i enter is in the list, but it isnt.
Jack Cowley
Put a Stop at the beginning of your code. When you enter something that is not in the comob box the code editor will open. Step through the code and see if your varible (NewData) is what you expect.
ack
killerwhale65
if stop is just the word Stop, then i'm afraid the code window does not open sad.gif
Jack Cowley
CODE
Private Sub Combo20_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
[b]Stop[/b]
On Error GoTo Err_ICAO_NotInList
...rest of your code..


EDIT - Disregard the b's in brackets in the code...

If you the above and then enter a new item into the combo box the code editor will open and the word Stop will be highlighted. Use the Step-Into Icon, on the menu bar, to step through your code. If you place the cursor over your variable then a tiny window will open by the cursor and tell you what value is in the variable.

If your code editor does NOT open then be sure you have your code in the right place, in the Not In List event of your combo box.

Jack
Edited by: Jack Cowley on Wed Dec 21 13:44:21 EST 2005.
killerwhale65
thats what i have.
checked and the code is in the notinlist event of my combo.
But it does not open.
Jack Cowley
Create a simple new form with just your combo box. Add the code to the Not In List event. See if that works. If it does then your current form is probably corrupt. You may be able delete the current combo box on that form and make a new one or you may have to make a new form from scratch. Give this a whirl and see what happens. By the way, you do have [Event Procedure] in the Not In List event don't you?
ack
killerwhale65
i think i know the cause. I also have this code:
CODE
Private Sub Combo20_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SKU] = " & Str(Nz(Me![Combo20], 0))
    [aantal in stock].SetFocus
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This is to display the current record for when i select an item that is in the list. I think it interferes with the notinlist code.
But how?
Jack Cowley
I do not know what is going on with your code(s) but I just did a quick trial and both codes worked for me. At this point I can only suggest you create a small form and add the combo box and add the code and see what happens.
You might want to try this:
CODE
    Dim Rs As Object
    Set Rs = Me.Recordset.Clone
    Rs.FindFirst "[SKU] = " & str(Nz(Me![Combo20], 0))
    If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
    Me.[aantal in stock].SetFocus

A hint - Spaces in object names are NOT a good idea as they can cause you a great deal of grief....
Jack
killerwhale65
i have tried a new form, but same result.
I have attached my DB.
Jack Cowley
Take a look at the table and at your combo box in the attached...
ack
killerwhale65
ok, you added a new PK with an autonumbering.
But in your attachment it is still not working. When i select something in the list the record is not even displayed?
Jack Cowley
My apologies as I forgot to change SKU in the After Update event to StockID. If you make that change then the 'seach' aspect of the combo will work. I did not spend a lot of time on this so it may not be perfect, but you do need to normalize your data and that is what I did by adding an autonumber primary key to your table.
ack
killerwhale65
Ha Wonderfull! This works!!!
Thanks!
Jack Cowley
I'm glad to hear that you have your form working. I strongly suggest that if you are using the SKU as your Primary Key that you abondon it, if it is not to late, and set up your tables with autonumber primary keys and join your related tables on those.
Good luck.
Jack
killerwhale65
ok will do thanks!
Jack Cowley
I'm glad to hear you are taking the plunge. It will pay dividends as you develop your database....
ack
killerwhale65
i'm still having an additional problem.
When i enter a new value in the list, i also want to move to that freshly created record to fill in the other controls. Right now it just creates the new record, but it moves to the first record instead of the new one.
Jack Cowley
To do that you will need to requery the form as it does not know that that the new data is in the table. I tried a requery in On Not In List event but that produced an error. Give it a fiddle and when I get a moment I will see if I can find an answer too...
ack
killerwhale65
yes i also tried the requery but it says that i get duplicate values then.
Jack Cowley
This thread has gotten long and I cannot come up with a solution. You might want to start another thread, giving specifics on what you want to do and let's see if some fresh eyes have a solution....
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.