Couple of issues with Forms and RecordSets

[DirtDiverKF]

Hi everyone, I have ran into a couple of issues that I could use some help with. First is easy, second maybe not so.

View attachment accessforums.zip I hope this attachment works!

The zip above contains 3 images to help show the issue I am having. The database I am working on is for holding local gaming tournaments on the Xbox. I am not worried about brackets and all that, but I am wanting to keep up with members and award points, show entry fees, etc.

I have a create tournament form that I will use to create the actual tournament. In this form I have the Tournament name, Location (which pulls from a different table of just locations), the game we are playing (which pulls from a different table of just games) and the tournament date.

This data will be saved to a table that logs the game id and the location id which are tied to this table with relationships. I can then query later to pull the data like address and all that.

The location and game are both combo boxes which transfers with them the id number which are auto numbers in the original tables, the locations and games tables. The combo box will show you the location name and city state and game will show the console and mature rating.

Thats what is going on, and is working fine, however, what if the location of the tournament dont exist and i want to add it on the fly? Or if the game dont exist and I want to add it on the fly? I added buttons next to these combo boxes that will pull up a form for adding a location. First issue is that the form opens with the first id item in the fields and I want it to be blank for a new location. What is the vba code to do this?

Second problem I am having is this, when i do fill in the add location form and hit save, it adds it to the table as it should, but when i go back to the create tournament form, the data is not in the combo box and i have to hit refresh to do it. I would rather not have to do this but would like the info in that combo box already. I have tinkered with some refresh vba commands (i get cant refresh now or something) and tried some requeries but no luck with either. I can gladly share the database with anyone that needs it to work through. The code below is the code for my create tournament button.

Private Sub cmdSend_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varTextData As String
Dim varTextData2 As String
Dim varTextData3 As String
Dim varTextData4 As String

varTextData = LocationBox
varTextData2 = GameBox
varTextData3 = DateBox
varTextData4 = NameBox

Set db = CurrentDb
Set rst = db.OpenRecordset("tblTournaments", dbOpenDynaset)

    rst.AddNew
        rst!lID = varTextData
        rst!gID = varTextData2
        rst!tDate = varTextData3
        rst!tName = varTextData4
    rst.Update

rst.Close
db.Close

Me!LocationBox = ""
Me!GameBox = ""
Me!DateBox = ""
Me!NameBox = ""

End Sub

 

[vbaInet]

Welcome to the forum DirtDiverKF!

Short, sweet and most especially to the point is always the best way to go because it saves.

Here are the aspects of your post I picked up.

Thats what is going on, and is working fine, however, what if the location of the tournament dont exist and i want to add it on the fly? Or if the game dont exist and I want to add it on the fly? I added buttons next to these combo boxes that will pull up a form for adding a location. First issue is that the form opens with the first id item in the fields and I want it to be blank for a new location. What is the vba code to do this?

You can force it to open to a new record using:

DoCmd.RunCommand acCmdRecordsGoToNew

Second problem I am having is this, when i do fill in the add location form and hit save, it adds it to the table as it should, but when i go back to the create tournament form, the data is not in the combo box and i have to hit refresh to do it.

Requery your combo box after the save code is run.

Forms![COLOR=Red]FormName[/COLOR]![COLOR=Red]ComboBoxName[/COLOR].Requery

Amend the red bits.

 

[DirtDiverKF]

Forms!FormName!ComboBoxName.Requery

Worked like a charm on that, on the open form, whats the simple vba code to open a form, etc. Right now im just using a macro button and I do not see how to tell that macro to do a empty record. Thanks a ton for the help, that requery worked like a charm.

 

[vbaInet]

I think in a macro you can use the GoToRecord action and set the Record column to New.

 

[vbaInet]

Plus I think there's RunCommand too.

 

[DirtDiverKF]

actually in the macro under data mode if you change it to add it clears the form, sweet. thanks man, this was a huge help. Ill post any other issues i come across as I go, thank you a million times over.

 

[vbaInet]

Yep, there's that one too but the only reason why I didn't tell you about that is because you won't be able to view old records in that mode. So it's up to you what effect you want.

Happy developing!