Solved Problems saving data from a Combo box (1 Viewer)

lodmark

Member
Local time
Today, 22:42
Joined
Jul 24, 2020
Messages
232
Hi @MajP
I have trouble saving new artists in one of the combo boxes on my form. This applies to the form frm_record2 and the combo box called "Kombinationsruta25" and is located at the top in the middle of the form. The other that you created and is in the original position hidden and only shows up when I select "Varierande artister" fingers exactly as it is intended. What happens is when I enter an artist in the first combo box who is not in the list (table) I get the question if I want to add this artist and I answer yes, then the form frm_new_artist comes up and I confirm by clicking on "Save and close ".. Then an error message appears stating that the artist is not in the list.
Since there are similar procedures that lead to this for both combo boxes, I can not find where the error lies.
The database is attached.
 

Attachments

  • Skivsamlingen.zip
    903.5 KB · Views: 91

Gasman

Enthusiastic Amateur
Local time
Today, 21:42
Joined
Sep 21, 2011
Messages
14,047
Here is how I handled the situation in one of my combos
Code:
Private Sub Crew_ID_NotInList(NewData As String, Response As Integer)
'Dim strSurname As String
'NewData = Left(NewData, InStr(NewData, " ") - 1)
'Response = AddNewToList(mixed_case(NewData), "Crew", "Surname", "Crews", "frmCrew")
    Dim txtSurname As String, txtInitials As String, strPKField As String
    Dim intNewID As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    Response = acDataErrContinue

    If MsgBox(NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

        strSQL = "SELECT * from  Crew WHERE 1 = 0"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSQL)
        txtSurname = Left(mixed_case(NewData), InStr(1, NewData, " ") - 1)
        txtInitials = UCase(Trim(Mid(NewData, InStr(1, NewData, " ") + 1)))

        rs.AddNew
        rs!Surname = txtSurname
        rs!Initials = txtInitials
        strPKField = rs(0).Name                  'Find name of Primary Key (ID) Field
        rs.Update

        rs.Move 0, rs.LastModified
        intNewID = rs(strPKField)

        'DoCmd.OpenForm strNewForm, , , strPKField & "=" & intNewID
       
        Response = acDataErrAdded
MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    Else
        Response = acDataErrDisplay
    End If
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:42
Joined
May 21, 2018
Messages
8,463
The issue I believe is probably my fault. I set this up as a find as you type. This is probably a good use for a Find as you type since you have over 1000 items in the list. However, there is an issue with the FAYT in that to requery the comboboxt you have to use the requery method i put in the class instead of trying to requery the actual combo.

C#:
Private Sub Kombinationsruta25_NotInList(NewData As String, Response As Integer)
    Dim lngID As Long
    If MsgBox("Artist: " & NewData & ", är inte i listan." & vbNewLine & vbNewLine & _
        "Vill du lägga till artisten / gruppen?", vbQuestion + vbYesNo) = vbYes Then
        DoCmd.OpenForm FormName:="frm_new_artist", WindowMode:=acDialog, OpenArgs:=NewData
        Response = acDataErrAdded
        FAYTartist.Requery
       'do not use Kombinationsruta25.requery
    Else
        Response = acDataErrContinue
    End If
End Sub

The reason the other combo works is because I did not set it up as a FAYT. If you want to make it FAYT, you will just have to do the same requery.
 

lodmark

Member
Local time
Today, 22:42
Joined
Jul 24, 2020
Messages
232
The issue I believe is probably my fault. I set this up as a find as you type. This is probably a good use for a Find as you type since you have over 1000 items in the list. However, there is an issue with the FAYT in that to requery the comboboxt you have to use the requery method i put in the class instead of trying to requery the actual combo.

C#:
Private Sub Kombinationsruta25_NotInList(NewData As String, Response As Integer)
    Dim lngID As Long
    If MsgBox("Artist: " & NewData & ", är inte i listan." & vbNewLine & vbNewLine & _
        "Vill du lägga till artisten / gruppen?", vbQuestion + vbYesNo) = vbYes Then
        DoCmd.OpenForm FormName:="frm_new_artist", WindowMode:=acDialog, OpenArgs:=NewData
        Response = acDataErrAdded
        FAYTartist.Requery
       'do not use Kombinationsruta25.requery
    Else
        Response = acDataErrContinue
    End If
End Sub

The reason the other combo works is because I did not set it up as a FAYT. If you want to make it FAYT, you will just have to do the same reque

The issue I believe is probably my fault. I set this up as a find as you type. This is probably a good use for a Find as you type since you have over 1000 items in the list. However, there is an issue with the FAYT in that to requery the comboboxt you have to use the requery method i put in the class instead of trying to requery the actual combo.

C#:
Private Sub Kombinationsruta25_NotInList(NewData As String, Response As Integer)
    Dim lngID As Long
    If MsgBox("Artist: " & NewData & ", är inte i listan." & vbNewLine & vbNewLine & _
        "Vill du lägga till artisten / gruppen?", vbQuestion + vbYesNo) = vbYes Then
        DoCmd.OpenForm FormName:="frm_new_artist", WindowMode:=acDialog, OpenArgs:=NewData
        Response = acDataErrAdded
        FAYTartist.Requery
       'do not use Kombinationsruta25.requery
    Else
        Response = acDataErrContinue
    End If
End Sub

The reason the other combo works is because I did not set it up as a FAYT. If you want to make it FAYT, you will just have to do the same requery.
Thanks @MajP
I suspected it was your "fault". ;) Like I suspected that a requery was needed, but I was unsure where I should place it to be right. I had tried to do a requery on the combo box but it did not work at all.
Now it works as intended. And I'll try to add FAYT to that box. I need to be able to search the database in several different ways so it is another project I have in front of me.
You may have noticed that I added two fields to the form. One with the genre and one with which media the song is on. The last one I do not get to work as I want, but I will return with a question about this later.
Thank you so much and have a good time.
Leif
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:42
Joined
May 21, 2018
Messages
8,463
I do not know if you use Media Monkey but that has a great interface for searching. I have about 30k of songs and it is super easy at finding and filtering. 1000 times better than iTunes.
I need to be able to search the database in several different ways so it is another project I have in front of me.

I think you could replicate this interface. If interested I could help. Take a look at these threads



Basically you could do this with 5 subforms. A tree view on the left and a continuous subform on the right with all columns sortable and filterable.

MediaMonky.jpg
 

Users who are viewing this thread

Top Bottom