Auto-fill form fields from query

[Poohbear0471]

I'm building a database to track alliance members in a multiplayer online game I play. I have two forms to track participation in two different events (Titan and War). One has the player name plus three fields, the other is the player name and two fields. I would like my forms to automatically populate the names of all of my active members (an enforced maximum of 30). I'm currently using combo boxes in datasheet view, so I need to populate each player manually. Suggestions?



On a side note, I know there's a lot of work to do on it. It kinda grew more than I expected and I missed a lot of best practices, so kindly ignore those. But any other suggestions are welcome.

Thanks!

[June7]

Exactly which table do you want these records in - TitanDailyData?

You want to create a batch of records for an event?

INSERT INTO TitanDailyData(TitanDate, Member) SELECT [enter some date], Player FROM Members;

Why don't you save TitanID instead of date into TitanDailyData? Why aren't you saving MemberID as foreign key instead of Player? TitanID, WarID, and MemberID are defined as primary keys. Primary key should be value saved as foreign key. Relationships should be set on primary and foreign keys.

[Poohbear0471]

Like I said, it grew faster than I was expecting and have a lot to clean up.

The Insert statement would go in the On Load event, correct?

[June7]

Well, try it but I think a button on parent form would be better or AfterUpdate of an input control.

I forgot # delimiters for date field.

CurrentDb.Execute "INSERT INTO TitanDailyData(TitanDate, Member) SELECT #[enter some date]#, Player FROM Members"

You understand need to put actual date in place of [enter some date]. The date input can be reference to a form field/control.

CurrentDb.Execute "INSERT INTO TitanDailyData(TitanDate, Member) SELECT #" & Me.TitanDate & "#, Player FROM Members"

Need parent record in TitanDaily first. So run code to save TitanDaily record if it is a new record then run the INSERT for dependent records.

Better have TitanDate and Member set as compound index No Duplicates in table design.

[Poohbear0471]

I removed TitanDate from TitanDailyData and set the DailyID as the foreign key and fixed the MemberID issue.

I have the following code attached to a button (I double checked my updated fields match correctly).

Private Sub FillRoster_Click()

CurrentDb.Execute "INSERT INTO TitanDailyData(DailyID, MemberName) SELECT Me.DailyID, MemberName FROM MembersQuery"

End Sub

I open a new form (TitanDaily), and populate the parent record so the DailyID autonumber is saved. I click the button to run the code and I get an error message that I'm missing parameters. "Run-Time Error '3061': Too few parameters. Expected 1." I've searched microsofts VBA reference for "Execute" and "CurrentDb.Execute" and I can't find what parameters it's looking for. I have the code pop-up help turned on. It shows Execute(Query as String, [Options]), but I can't find what those options are.

Help says it could be an unknown field name (mis-spelled, doesn't exist, etc.), but I double and triple checked that.

Any idea what I did wrong?

[June7]

Note the concatenation on my example. You are not doing that in your code.

You need to pull DailyID from the form. This is a variable input. Do not put variables within quote marks. Reference to form control is a variable input.

Me. is a qualifier alias for the form name the code is behind.

Should still have compound index to prevent duplicate pairs.

[Poohbear0471]

Thank you! I got it working. I have to refresh the form after I run it to get it to display, but it's working. Thank you SO MUCH!