Turn combo box w/ two columns to 2 combos

[pbaldy]

Does this do what you want?

  Dim lngVessel               As Long
  If IsNumeric(Me.VesselInspectionID) Then
    lngVessel = Nz(DLookup("VesselID", "tblVesselInspections", "VesselInspectionID = " & Me.VesselInspectionID), 0)
    Me.CmbVsl = lngVessel
    Me.CmbIns.Requery
  End If
  Me.AllowEdits = Me.NewRecord

[shariq1989]

Yea, that worked great. I'll do a bunch of data entry to see how well it holds up and then get back to you. Thanks again!

[pbaldy]

No problemo! Do I get a free ride on a ship now?

[shariq1989]

Haha only if you are deemed a 100% Not Suspicious Person :-P

[pbaldy]

Bummer; I've got the beady eyes that always get me stopped.

[shariq1989]

Haha darn. Well the issues, I am hitting right now are.. The form that you just gave me the code for works in that it lets me see previous entries without having to mess with the combo boxes, but what would the user do if he needs to edit anything? Presently, the date on a previous record cannot be selected.

Another question I have is what is to be done about repeats? What if someone accidentally re-enters a record?

[pbaldy]

Well, this code that you already had in there is stopping edits on existing records:

Me.AllowEdits = Me.NewRecord

You can test for duplicates in the before update event of the form, or set an index on the appropriate combination of fields and Access will enforce it for you. I tend to test myself.

[shariq1989]

That worked out. Moving onto building reports and having them calculate when the next inspection is coming up based on the latest inspection record... for each inspection. Gonna be tough. Then, I gotta work on the whole machinery overhaul part of it. Meanwhile.. July 31st is creeping up.

[shariq1989]

My question right now is whether it is better to have a new field in the Inspection Records table that uses the DateCompleted and InspectionInterval fields to calculate the Due Date or should it just be in the report?

[pbaldy]

In similar applications, I just use the most recent date (or mileage) completed and the interval and calculate the next due date/mileage for "due for service" and other reports. I don't have a field for it in any table.

[shariq1989]

So I ended up writing my very first SQL query...


SELECT TblInspectionHistory.VesselInspectionID, Max(TblInspectionHistory.DateCompleted) AS MaxOfDateCompleted
FROM TblInspectionHistory
GROUP BY TblInspectionHistory.VesselInspectionID;

...and it works wonderfully! Sorts all vesselinspectionIDs by the latest date. Now I get to figure out how to get it to work with the rest of my system..

[shariq1989]

Just an update.. my project has turned into a three headed monster. A manager from another department noticed from what the database is capable of and wants his stuff in there too. And my boss wants the database to handle cost and budgeting, have user logins and relevant scanned documents based on each record entry. All of of this seems possible... I just need to collect my thoughts, put them down on paper and figure out how this is going to work..

[pbaldy]

Good luck!

[shariq1989]

So tomorrow I am sitting down with my boss and kinda showing him a glimpse/overview of how the database will work. Any good guides on creating the first UI screen that pops up, where buttons that lead to forms can be inserted. Not talking about a splash screen but about what comes after one.

[pbaldy]

If you mean style-wise, I'm not the one to ask. I just create forms that provide the desired functionality. I'm not good at making them pretty.