code problem

[dat_guy]

Greetings forum,

I'm pretty new to access and VB and am having some trouble figuring out how to fix a coding problem.

I have 2 tables, tblGroup and tblBanquet. Additionally I have 2 forms, frmGroup and frmBanquet, both based on queries of the corresponding tables.

One-to-Many relationship from tblGroup-Group Name to tblBanquet-Group Name

Primary Keys:
tblGroup -
Group Name

tblBanquet -
Group Name
Date of Function
Arrival Time
AmPm

The frmGroup works well, its pretty straight forward.

However, I have a cmbBox (cmbGroupName) in the form header on frmBanquet used to pick group names (used as customerID) and place that value into txtGroupName (bound to Group Name in qryBanquet -> tblBanquet). Additionally, in the same header I have a cmbBox (cmbBanquet) used to pull up existing records from qryBanquet.

Either of these controls work fine by themselves. However, if I would use cmbGroupName to pick a group name, and then use cmbBanquet to view a current record without filling pri key requirments (ie not creating a new record) the operation fails. This is an action I forsee users atempting on a regular basis.

I dont know how to fix this problem, and appreciate any help given. I attached my problem children in 97 format.

thanx in advance
dat_guy

 

[Fizzio]

A few things.

1.Because of your reference to the MouseWheel (Which I do not have) your Db would not work correctly anyway without the missing reference.

2. You should create a new field in both tables GroupID which should be an Autonumber in your tblGroups and a Number in the tblBanquet. Link the 2 tables via this field.

3. You are using the combos to search for a record rather than filtering the recordset. I would use the first combo to filter the recordset to show all bookings by that group and use the second combo to find a specific group.

I've posted some changes but have been unable to test them due to the Mousewheel issue. I've put some comments in the table design of tblBanquet as well which should give you some other ideas.

 

[dat_guy]

First, thank you for your reply, it is very much appreciated.

On your points,
1) I removed all MouseWheel code

2) I dont know how to get this to act the way I need it to. Each record in tblBanquet MUST match with a record in tblGroup. How do I ensure that GroupID in tblBanquet matches the appropriate GroupID in tblGroup?
I'm assuming the best way might be to make a hidden txtBox on the form bound to GroupID on tblBanquet. But I dont know the code to get the correct ID into that box. Or am I way off here?

What are the benefits to this? Everything I've read says the same thing, but doesn't explain the benefits.


3) I'm currently reading up on the filter function, and bookmarks. Like I said, Im stilll new to this.
But it seems that even though the two cmbBoxes have different code, they are doing the same thing at present time. I placed some questions in the code if you wouldn't mind taking a look.

thank you very much for you help
dat_guy

 

[Fizzio]

1 Thanks

2. The relationship is set up for GroupID. On your banquet form I have changed the GroupName textbox to a combo which now selects the Group ID.

3. Replies on your code.

 

[dat_guy]

Thank you for your help so far. I have certainly gained some valuable knowledge.

But, my original symptom still perisists. If a user picks a GroupID, and then decides not to complete that record, but instead pick an existing record to edit, when they attempt to pull up an existing record, the operation will fail because Access trys to write to the table but some primary key fields are null.

I dont know where to go to find the solution to this.

dat_guy

 

[Fizzio]

Ahhh. That makes it a little clearer - I misunderstood your question initially.
So you want to validate the form and offer the user to cancel the input if they want - Try this then - fairly simple. The fields with red borders are required fields and I have set the tag to NumberRequired if a number is expected and Required for all else (the validate code is courtesy of Rich)

Have a look at the code to see how this works.

 

[dat_guy]

It works!

Hey reading it now, I dont even fully understand my first post. I leanred more going the long way anyway.

This is exactly what I needed. This is great. The DoCmd.CancelEvent was the missing key. I tried cancel = true and all kinds of other code that got me "almost there".
I attached a copy of where I was when your reply was posted if you wanna take a look. The code is kinda crazy looking.

Again thank you very much. Your help has been invaluable.

dat_guy

 

[dat_guy]

ok here is the file

 

[Fizzio]

Yes that works just as well. You now have two possible options.
Glad to be of help