Solved Help with form coding (1 Viewer)

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
Hi I'm back!
I have now fixed the errors I made on the form that @arnelgp helped me with.
Now it works and I have continued to add more vinyl records to the database.
As I told you before, the basis of this database is an Excel sheet with music in my disco radio programs on Halmstad's local radio.
The programs are in Swedish but feel free to listen on Thursday evenings at 20:00 at http://radiohalmstad.se

My question concerns the form where I want to add music to new programs but also fill the list with music in the programs I have already done.
My Excel sheet contains the music linked to the artist and the date the program was broadcast.
This is now entered into different tables, tbl_artist, tbl_music, tbl_program, tbl_program_date and tbl_program_music where the last table connects the programs with the music because a song can appear in several programs.
The form is called frm_program and I have entered the songs for the first five programs by hand.

Now I want to be able to select program six in the form and then start adding the songs.
This is not possible. In the subform to frm_program where the songs appear, I have tried to add two combo boxes, one for the artist and the other for the music.
The idea is that it should only be these two when everything works.
When you have selected the artist in one combo box, only that artist's songs should be possible to select in the combo box for the music.

As always, I appeal to you for help with the code for this.
Is there anyone who is interested?

Leif
 

Attachments

  • records_all_english (1).zip
    432.5 KB · Views: 505

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
Sorry for that @Gasman
I'm apparently not that patient.
But of course understand and should calm me down a bit with my demands.
When the database is here, I do not feel that I can add any records to it, hence my impatience.

I have updated the database with code that makes the two combo boxes cascaded.
Unfortunately I get a question when I start the form frm_program or when I change "post". I do not know why but it indicates that there is something wrong when ID for the field is to be retrieved. Read my post above for information about what the forma shall do.

Thanks!

Leif
 

Attachments

  • records_all_english.zip
    446.9 KB · Views: 510

Gasman

Enthusiastic Amateur
Local time
Today, 02:06
Joined
Sep 21, 2011
Messages
14,048
Well I'm sorry I can't help. I cannot open the DB as I have a very old version of Access.
Hopefully someone will come along and assist.

Might be worth posting exactly what that question is? and what you mean by 'change post' as well?

People are very good here, but they are not mind readers. :)
 

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
With "change posts" I mean when i go to the next record in the form.
Because my version of Access is in Swedish posting the messege won't help.
But it says exactly the same thing that is in terms of the question I created.
Something like this:
Enter parameter value "[Forms]![frm_program_music_order_subform2]![cmb_artist_name]" and after that a filed for input.
Skärmklipp.PNG


I hope this will help a bit!

Leif
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:06
Joined
Sep 21, 2011
Messages
14,048
OK, so that is probably because you are referencing a form value in the query for the form. ? In fact you appear to be referencing a subform control.?
I am not that up with the correct syntax for that, but it is normally along the lines of MainForm.SubformControl.Forms.ControlName.?

Use the builder to get the correct syntax. However that brings the question, how can you supply a value, if the form that is using that recordsource is not open at the time.? If that is what is happening. Should be fine AFTER the form loads and you change the artist name which I would assume is a combo perhaps.?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:06
Joined
May 7, 2009
Messages
19,169
nobody seems to entertain you. (ithinkthisDomsprefertohelpGirls).
take a look at Form1.
 

Attachments

  • records_all_english (2).zip
    272 KB · Views: 507

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
nobody seems to entertain you. (ithinkthisDomsprefertohelpGirls).
take a look at Form1.

Really @arnelgp It's 2020 and MeToo rules the world! :LOL:
The form is interesting and wow you're quick.

A small change if I may!
The programs are the essence of this.
The dates on which they were broadcast should be linked to the programs.
Now it's more that you choose the date to get a program.
Not a bad idea but better if you can only see the dates on which the program was broadcast.
Like the dates appear as I had in my first suggestion in frm_program.
And that you can then click on the next program through the next post.
If I understand correctly, you have chosen the dates as the main item.
I'm amazed how you managed to get this form1. Nice!

Thanks!

I will try to add music and programs tonight, while I listen to my program at 20:00.
For best listening use the app TuneIn and search for Radio Halmstad.

Leif
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:06
Joined
May 7, 2009
Messages
19,169
i made some modification, the previous one doesn't create new program in tbl_program.

i have no idea how you make the program.
if it it is done, daily or prepare days ahead, the
date listing is ideal. you only go to a specific date
and it will show what program is stored for that day
and what songs are in line.
 

Attachments

  • records_all_english (2).zip
    749 KB · Views: 505

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
i made some modification, the previous one doesn't create new program in tbl_program.

i have no idea how you make the program.
if it it is done, daily or prepare days ahead, the
date listing is ideal. you only go to a specific date
and it will show what program is stored for that day
and what songs are in line.

Thanks @arnelgp When I tested it last night with a new program it stored both number, titel and date in the right tables and got the few songs I put in right as well. So I think you got it right the first time. But I look in to this version later today.

My programs are made for about a month before they are broadcast, so nothing is broacasted live. They are then broadcasted ones a week.
The date is just a reminder for me and it's good to be able to see what date each program has been broadcasted.
In the long list of dates that shows only when you klick on a date. I would like to see all dates for one program at the same time.
I hope I could explain it well.
Leif
 

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
@arnelgp In your new file I was trying to look in to the subform subFormProgramDate but when i started the form I've got a program run error 2452 with this text "The expression has an invalid reference to the parent property"
I dont know the importance of the message, The form works fine i I open the Form1.
Leif
 

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
@arnelgp In your new file I was trying to look in to the subform subFormProgramDate but when i started the form I've got a program run error 2452 with this text "The expression has an invalid reference to the parent property"
I dont know the importance of the message, The form works fine i I open the Form1.
Leif
Same message is in the file you've posted yesterday.
Leif
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:06
Joined
Sep 21, 2011
Messages
14,048
@arnelgp In your new file I was trying to look in to the subform subFormProgramDate but when i started the form I've got a program run error 2452 with this text "The expression has an invalid reference to the parent property"
I dont know the importance of the message, The form works fine i I open the Form1.
Leif
If you have a subform, then it must be opened with it's parent.?
That is why it is called a subform?
 

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
If you have a subform, then it must be opened with it's parent.?
That is why it is called a subform?
OK @Gasman I have not been affected by this before as I often work both in the parent and in the subform. Maybe tht's wrong, but it has not been a problem.
Leif
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:06
Joined
Sep 21, 2011
Messages
14,048
If you want to work on the subform, open the main form in design view and then work on the subform from there.?

You can still open it by itself if you wish, just accept the errors shown to you like the error you have already reported.?

It all depends on what code is referring to the parent.?
 

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
Hi @arnelgp
New file to work with.
No change of form or code.
I have used the weekend to, using the forms you made, add songs to the programs. It has worked extremely well.
But I miss the ability to switch between programs using the record selector.
And then also the possibility to see what dates this particular program was broadcast.
If you look at the form frm_program that I made myself from the beginning, that's how I wish it worked.
frm_program.PNG

And I want to be able to create new programs and add dates to old ones if these were to be rebroadcast. But the way you created to add songs to the programs works perfectly.
Hope you want to help me with this

Leif
 

Attachments

  • records_all_english.zip
    361.3 KB · Views: 284

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
Tonight I've used the subform subProgramMusic that you created @arnelgp in my form frm_program and it work like a dream.
Both adding songs to old programs and creating new programs and adding songs to them.
Still struggling with adding dates.

Leif
 

lodmark

Member
Local time
Today, 03:06
Joined
Jul 24, 2020
Messages
232
Hi @arnelgp Can you please explain what this code does?

In my form frm_program I can't add a new date to an old program neither to a new one if I created one. Nothing happens in the subform.
I think it has to do with the fact that I do not manage to put the ID field in the table for the dates of the programs.
Maybe something with the function NextAutonumber?
With your form and your code that I pasted below, it works.
I need this code for my form.

Option Compare Database
Option Explicit

Private lngID As Long


Private Sub Form_BeforeInsert(Cancel As Integer)
lngID = NextAutonumber("tbl_program_date", "program_date_ID")
End Sub

Private Sub Form_Current()
With Me.Parent
!txtProgramNumber = Me.program_nr
If lngID > 0 Then
!txtDateID = lngID
Else
!txtDateID = Me.program_date_ID
End If
End With
If lngID > 0 Then
With Me.RecordsetClone
.FindFirst "program_date_id=" & lngID
Me.Bookmark = .Bookmark
End With
lngID = 0
End If
On Error Resume Next
Forms!Form1.fixCombo
On Error GoTo 0
End Sub

Private Sub Form_Load()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.FindFirst "program_date=#" & Format(Date, "mm\/dd\/yyyy") & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Private Sub Form_Open(Cancel As Integer)
With Me
!program_date_ID.ColumnHidden = True
!program_nr.ColumnHidden = True
End With
End Sub

Private Sub Form_Undo(Cancel As Integer)
lngID = 0
End Sub
 

Users who are viewing this thread

Top Bottom