A multitude of little ideas / problems

[Gord0]

Hi Guys,

Developing a database for a small business consultancy.

I have a few ideas which i am not quite sure how to implement, i'm trying to use as much VBA as possible to do these little tweaks.

1) I'm not currently using an Autonumber for my CompanyID or my EnquiryT, QuotesT and ProjectT ID (One company can have many Projects etc, and these ID's are shared in Enquiries and Quotes so they can easily be identified throughout the process). However i would like to be able to "suggest" a new ID when creating a new record in Form view which would equal the most recent record +1. I could change the CompanyID to an autonumber if needs be, however due to the kind of relationship in Enquiries-Quotes-Projects it's a no go.

2) I have a ComboBox which drops down from CompanyID in multiple forms which allows for Companies already in the database to populate the relevant fields using Dlookup however when you select from the ComboBox, the actual CompanyID isn't visible in the CompanyID field. Is this something relating to a refresh?

3) I want my ComboBox (same one) to confirm that you want to make changes to a record if there is already information in a record i.e. Trying to stop users changing the CompanyID in existing records and messing up the info. So a simple warning like "Are you sure you want to amend data for this record?" Yes/No.

I think that is all, i'm still learning and got lots of info from the site already, many thanks to all.

 

[Gord0]

Hi Guys,
2) I have a ComboBox which drops down from CompanyID in multiple forms which allows for Companies already in the database to populate the relevant fields using Dlookup however when you select from the ComboBox, the actual CompanyID isn't visible in the CompanyID field. Is this something relating to a refresh?

Solved... Property Sheet for ComboBox > Control Source & Set to Field needed.

 

[Frothingslosh]

Gord0, as a rule, if you figure out a solution, it's considered polite to post it into your thread just so that if someone in the future searches and your thread comes up, they can find the answer.

For problem 3, put the message box code in the 'Before Update' event on the combo box, and make it so that if the user selects no, the update is cancelled.

For problem 1, there are a few ways you can do it. The easiest to code is the slowest to execute - in the control, set the default value for the CompanyID to DMax("CompanyID","CompanyTableName") + 1.

 

[Gord0]

Okay, edited my last post as per solution

Great i shall have a go with both and post whether i've been successful and how i did it when its sorted.

Thanks again