Search If...Then...

[Joshua61679]

I've got two forms: NewJob & Cased1, both attached to tables. Both forms have fields Customer & Lease, along with other fields. I need to code a command button on NewJob to open Cased1, search the records for a match in both the Customer & Lease fields, & if there is a match to open that record, and if there is not, to create a new record, moving the information from Customer & Lease to the corrisponding fields. Is this possible? Any help would be much appreiciated. Thanks.

Joshua R. Coffee

 

[JeremyNYC]

Here's how I'd do it: make a recordset that looks for a matching record. If there is a record, grab the ID from the recordset and open the second form filtered to just that record. If there are no records, open the form in add mode, populating the two fields on the second form from the variables used in the recordset.

Like this:

Private Sub Command6_Click()
On Error GoTo Error
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strWhere As String
Dim strCustomer As String
Dim strLease As String

Set db = CurrentDb
strCustomer = Me!txtCustomer
strLease = Me!txtLease
strWhere = " WHERE Customer = '" & strCustomer & "' AND " _
& " Lease = '" & strLease & "'"
strSql = "SELECT WhateverID, Customer, Lease " _
& " FROM tblWhatever " & strWhere
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)
If (rst.BOF And rst.EOF) Then
Call DoCmd.OpenForm("frmWhatever", , , , acFormAdd)
Forms!frmWhatever!txtCustomer = strCustomer
Forms!frmWhatever!txtLease = strLease
Else
Call DoCmd.OpenForm(frmWhatever, , , strWhere, acFormEdit)
End If

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Exit Sub

Error:
Call MsgBox(err.Number & ": " & err.Description)

End Sub

You will, of course, have to adjust names of objects here, but this should be a decent starting point. I've not tested it, so there may be a problem or two. Just get back to this thread if you have any trouble.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Joshua61679]

Jeremy,

Had to make an strWhere1 definition without the WHERE in it for the OpenForm command, but other than that this worked great. I bow to your knowledge of the force, I mean Access, thanks a lot.

Joshua R. Coffee

 

[JeremyNYC]

Joshua,

Well, as you see, it's not to be bowed to, but I am glad my knowledge could help you out.

Cheers, and happy holidays.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.