Create a form to Add a new record 1

[ckeener]

I have a main switchboard form with a list box. The user needs to be able to click a command button to create a new record in a table with a new "SongRef" (the autonumber key for the table) Then it should open a "frmSongs" form that has a RecordSource of the "tblSongs" (the table where all the information from the songs is located) based on the new record that was just created. This is what I have already, I just don't know how to create a new record

Private Sub cmdAddSong_Click()
On Error GoTo Err_cmdAddSong_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSongs"
DoCmd.OpenForm stDocName, acNormal, "", "SongRef = Forms!frmSwitchboard!lstSongs", , acNormal


Exit_cmdAddSong_Click:
Exit Sub

Err_cmdAddSong_Click:
MsgBox Err.Description
Resume Exit_cmdAddSong_Click

End Sub

I know it should be simple but I don't where to start. Any help would be greatly appreciated. Thanks.

 

[THWatson]

ckeener
The code to go to a new record in the form is
DoCmd.GoToRecord , , acNewRec

In other words, behind your command button, include in this in your code...
stDocName = "frmSongs"
DoCmd.OpenForm stDocName, acNormal
DoCmd.GoToRecord , , acNewRec

Tom

 

[ckeener]

Tom,
thank you, but that doesn't create a new record based on a new SongRef does it? I want the code to create the record's Songref automatically. (I guess I chould have been more specific in my post.) I am trying to get the record created before I open it so that there is an existing record to open. My reasoning behind this is that I can use the same code and process to open a record for editing.

Thanks, Caleb

 

[jimb0ne]

if you use gotorecord, the songref should be the next autonumber which is going to be assigned. It will automatically create a number, just as if you'd used .addnew

 

[ckeener]

Thanks, I guess I have to back up a little and change something though. I checked your suggestion and it worked with an autonumber. I inherited the tables in the database from another project and I assumed that the SongRef was autonumber (yes, i know what assuming does, it made an a** out of me this time anyway. Sorry) Songref is actually a long integer number that starts at 10000 and goes up. Any sugesstions for adding a number and creating a new record that way???

 

[THWatson]

ckeener
Here's a suggestion you could try and see if it works for your situation.

Put the following code on the Open event of your frmSongs.

Dim intTemp As Integer
intTemp = DMax("SongRef", "tblSongs&quot
If Me.NewRecord Then
SongRef = intTemp + 1
End If

Hope that helps.

Tom

 

[ckeener]

Thanks Tom,

That is a great help in setting up a new record as I open the form. Now how do I open the form based on the new record. I am not sure if I may have something set up incorrectly in the form itself or if I am just not able to call it properly.

The form's record source is tblSongs and the textboxs' ControlSources are from fields in the table: Title, SongNum, Tempo, etc. I can't seem to be able to make it open without getting boxes to enter parameter values.

I was thinking that if I could get the Add button to create the new record based on the SongRef Long Integer, then I would be able to use the same method to open that new record as to open another one for editing. I just can't seem to be able to get my form to open properly though.

Thanks for your help!
Caleb

 

[THWatson]

Caleb
I'm getting a little lost here, as to visualizing what you really can do, and can't do, and want to do now.

Are you saying that you have things working just fine now as far as adding a new record with the proper SongRef # is concerned? So that part is okay?

But now you want to have another command button which takes you to the last record you entered, so you can edit it?

If you want to go to the last record you entered, the code to do that is
DoCmd.GoToRecord,,acLast

In other words, from your Switchboard form a command button that has this code...
stDocName = "frmSongs"
DoCmd.OpenForm stDocName, acNormal
DoCmd.GoToRecord , , acLast

If I'm not understanding this correctly, post back.

Tom

 

[jimb0ne]

Hi Caleb,
If I understand correctly, the main thing you want to do is create a SongRef number which is unique and above 10000, I have code which I have written which will assign the next available number. Let me know if this is what you are looking for...

 

[ckeener]

Sorry to be so confusing. What I am trying to do specifically is this.

I have the switchboard form with a listbox of all the songs with their title, album, artist, songnum, etc. on the main switchboard as well; I have an Add button and an Edit button as well as a Delete button.

What I want to do is to have the user be able to press the Add button on the Main Switchboard and have the code create a new SongRef (Which is a long integer, not an AutoNumber) and then open another form, frmSongs, which has textboxes with all the individual information about the song so that the user can add all the relevant information about the song. Then when the user presses the OK button, everything is saved to the correct table, tblSongs.

I also want the user to be able to press the edit button and have the frmSongs open with the information from the song highlighted in the listbox. The user should be able to change the things he or she needs to and then press the OK button to update the record.

So far the code is good and has helped me, but not on this part of the project. I was actually trying to do something similar in another part of the project., so thank you.

Sorry that I have been so confusing but I hope this is a little clearer as to what I am trying to do in detail.

Thanks
Caleb

 

[THWatson]

Caleb
Now I think I get it. When the user presses the Edit button on the Switchboard, you want the code to open frmSongs at the record which has been selected in the List Box.

So your Edit command button would have code such as...
stDocName = "frmSongs"
DoCmd.OpenForm stDocName, acNormal
DoCmd.GoToRecord , , acGoTo, YourListBoxName.Column(0)

The code line I have added assumes that your bound column is Column 1...but the tricky part here is that Access numbers the columns starting at 0, so if your bound column is Column 1 you use Column 0.

You will also, of course, have to plug in the name of your ListBox on the Switchboard.

Does this do it?

Tom

 

[ckeener]

It is giving me a runtime error saying I can't go to the specified record. The debug points at

DoCmd.GoToRecord , , acGoTo, YourListBoxName.Column(0)

frmSongs recordsource is the tblSongs and the controlsource of the textboxes are the fields from the tblSongs. I thought the key (SongRef) would be able to be used to go to a record as long as the recordsource of the form was the table (tblSongs)

Am I set up incorrectly?

 

[THWatson]

Caleb
Am I right that you want to go to the record which is selected in your ListBox? If so, what is the name of the ListBox? And which column in the ListBox is the "bound" column?

Tom

 

[ckeener]

Tom
Yes, that is correct. I want the record that is selected in the listbox (which is on the main switchboard) to be the record opened with the second form (frmSongs).

The name of the listbox is lstSongs
The bound column is column 0
Column 0 is the SongRef (which is hidden in the listbox)

The code runs until it gets to
DoCmd.GoToRecord , , acGoTo, Me!lstSongs.Column(0)
I put the code in the Edit button's click function which is part of the main switchboard.

Thanks,
Caleb

 

[THWatson]

Caleb
Try this code behind your Edit command button on your main Switchboard form...

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSongs"
stLinkCriteria = "SongRef = " & Me.lstSongs.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

Let me know if this works.

Tom

 

[ckeener]

Tom,
It is working!!!!
Thank you so much for your help and patience!

Caleb

 

[THWatson]

Caleb
Whew! We finally got it!

Should have tried that method in the first place.

Tom