UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Do I Implement A Database Booking In System?, Access 2010    
 
   
mickey_the_mooch...
post Sep 25 2019, 03:58 PM
Post#1



Posts: 1
Joined: 25-September 19



I am not sure of the best way to explain this problem, so please bear with me…

I have inherited a database which is not normalised and has poor data integrity.

The business repairs car parts which are booked in to the workshop, then assessed, then fixed, scrapped or sent away for repair. A key problem is that there is no one table for the parts - the ASSETS table - containing the part's serial number etc. They appear in several different tables. I need to rectify this and I know the table schema that I want.

My problem is how to implement the booking in process so that the database 'looks' in the ASSETS table to see if the part already exists and if it doesn't, create it. If it does already exist, don't create a duplicate. In either case, a BOOKING IN record needs to be created to store date/time info for this event.

Do I record BOOKING info and ASSET info in a temp table, then separate them into two tables when the booking is complete? Do I record all the info in the BOOKING table then remove the ASSET info to its own table?

Sounds a simple problem - it's essentially the same problem as having a sales database which stores customer information in a CUSTOMERS table and then adds distinct new customers when they order for the first time. I'm sure there must be many solutions out there but I can't find them! Maybe someone could point me in the right direction….

Thanks for your help everyone 😀
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2019, 04:17 PM
Post#2


UA Admin
Posts: 36,180
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.


Without seeing the actual tables involved, we can offer a generic suggestion, but here's what I assume might work.

You need to take the value typed into a control on a form for the Asset part. Use the BeforeUpdate event of that control to check for the existence of that value in the PartNo field of the Asset table. Then you can decide whether to add it or not. If not, the user has to select the existing PartNo.

CODE
Private sub txtAssetPartNo_BeforeUpdate(Cancel As Integer)

    Dim strSuggestedPartNo as String
    Dim booExistingPartNo as Boolean

    strSuggestedPartNo = NZ(Me.txtPartNo.Text , vbNullString)
    booExistingPartNo = DCount("PartNo", "tblAsset", "tblAsset.PartNo = """ & strSuggestedPartNo & """") >0
    If booExistingPartNo = True Then
        Msgbox Prompt:="PartNo " & strSuggestedPartNo & " already exists in the asset table.", Buttons:=vbOkOnly, Title:="Duplicate PartNo"
        Cancel = True
     End If

End Sub

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 25 2019, 04:21 PM
Post#3


UA Admin
Posts: 36,180
Joined: 20-June 02
From: Newcastle, WA


Actually, I would not even use a text box.

I would use a combo box which gets its rowsource from the existing PartNo values in the asset table. Sort of like this:

SQL
SELECT PartNo
FROM tblAsset GROUP BY PartNo
ORDER BY PartNo


If you set the Limit to List property of this combo box to "Yes", Access will automatically validate existing/new PartNos.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Sep 25 2019, 05:39 PM
Post#4


UtterAccess VIP
Posts: 11,277
Joined: 10-February 04
From: South Charleston, WV


Add record from combo box

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 02:25 PM