claytonjgordon
Technical User
I would think this would be a common request and thus already covered in some thread or another but I've been looking for a few hours and didn't see any. If this has already been covered somewhere that you are aware of, please point me in the right direction.
Here goes:
I created a database that takes incoming requests from the main work group and queues up work for a support work group. The database was working as intended but the support group has indicated that they are getting duplicate request for the main group, i.e., employee one submits the request and then later employee two submits the same request not knowing that employee one has already done it.
The support group has asked that I somehow block this from happening.
The way I see it working is by creating a new form where they enter an account number and a specific date relevant to that request (not that actual days date, a date that drives the function the support group performs). These two things together act like a primary key. Either one can be duplicated, but not both. If they are, it’s a duplicate request.
So, on this new form they enter the account number and this special date and hit a button. Then I see it running a query or something somehow (this is the part I can't conceptualize) and it checks to see if there is already a record with both that Acct number and date. If there is, then I would want to generate a MsgBox saying something to the effect of "this is a duplicate request, please reference ID number" &_[Main table].ID; and then close the form and bring them back to the main menu.
If there isn't a matching record, then it takes the values in the two fields from the new form and populates them on the main request form where they can continue to fill out the form and everything else would continue as normal.
The part I can't figure out how to do is the check to see if there is a record on the main form that has the same account number and FLD(date)as entered on the form.
In a fumbling attempt to solve the problem I created this code which is supposed to create a temp table in the memory of any record that has a matching account number but it doesn't seem to be working and I don't know if I'm heading in the wrong direction to achieve the goal stated above.
Any help or insight you could provide would be greatly appreciated.
Thank you in advance!
Dominus Nihil
(Master of Nothing)
Here goes:
I created a database that takes incoming requests from the main work group and queues up work for a support work group. The database was working as intended but the support group has indicated that they are getting duplicate request for the main group, i.e., employee one submits the request and then later employee two submits the same request not knowing that employee one has already done it.
The support group has asked that I somehow block this from happening.
The way I see it working is by creating a new form where they enter an account number and a specific date relevant to that request (not that actual days date, a date that drives the function the support group performs). These two things together act like a primary key. Either one can be duplicated, but not both. If they are, it’s a duplicate request.
So, on this new form they enter the account number and this special date and hit a button. Then I see it running a query or something somehow (this is the part I can't conceptualize) and it checks to see if there is already a record with both that Acct number and date. If there is, then I would want to generate a MsgBox saying something to the effect of "this is a duplicate request, please reference ID number" &_[Main table].ID; and then close the form and bring them back to the main menu.
If there isn't a matching record, then it takes the values in the two fields from the new form and populates them on the main request form where they can continue to fill out the form and everything else would continue as normal.
The part I can't figure out how to do is the check to see if there is a record on the main form that has the same account number and FLD(date)as entered on the form.
In a fumbling attempt to solve the problem I created this code which is supposed to create a temp table in the memory of any record that has a matching account number but it doesn't seem to be working and I don't know if I'm heading in the wrong direction to achieve the goal stated above.
Code:
Private Sub StartRecordButton_Click()
Dim db As Database
Dim rs As Recordset
Dim SQL As String
SQL = "SELECT [Main Table].* " & _
"FROM [Main Table] " & _
"WHERE ((([Main Table].Acct_)= " & LoanNumber & ")) "
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(SQL)
DoCmd.Save
End SubAny help or insight you could provide would be greatly appreciated.
Thank you in advance!
Dominus Nihil
(Master of Nothing)