Select record or create new - If statement

[simon373]

Hi,

On a button click event, what VBA code could I use to check if a customerID exists in a related table, view that record in a form if it exists or (if it doesn't exist) create the record (and view in a form) using the primary key displayed on the current form as a reference?

Can anyone recommend a VBA programming book for a beginner to intermediate level, then perhaps I can try to answer a few questions instead of asking them!?

Many thanks

Simon.

 

[GhostWolf]

My method of choice is retrieving a recordset with dbOpenDynaset:

cmdSQL = "select * from TableName where KeyFld='" + InputValue + "'"
set rs = db.OpenRecordset (cmdSQL, dbOpenDynaset)
if rs.BOF and rs.EOF then
  .AddNew
    !KeyFld = InputValue
    TargetForm.KeyFLd = InputValue
  .Update
else
  TargetForm.KeyFld = InputValue
  TargetForm.NameFld = rs!NameFld
  ' etc, etc
endif
rs.Close
TargetForm.Show 1

Then, when the user's done and wants to exit TargetForm, build/execute an update SQL statement.

 

[JoeAtWork]

The best book by far is the Access Developers Handbook.

 

[simon373]

Thanks for this. I'm getting the following compile error: invalid or unqualified reference, on the line:

.AddNew

Running Access 2000. Any ideas on this one?

Thanks.

 

[PHV]

Use a With rs ... End With block.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GhostWolf]

Duh!! I should have put "rs.AddNew" and "rs.Update" there. (I usually use "With rs" for these, but didn't in the example.)

See if it works any better for you that way.

 

[simon373]

Still having some difficulty with this

Private Sub cmdOpenRecordCard_Click()
On Error GoTo Err_cmdOpenRecordCard_Click
Dim db As DAO.Database, rs As DAO.Recordset
Dim strSQLquery As String
Set db = CurrentDb()

strSQLquery = "SELECT * FROM tblRecordCard WHERE tblRecordCard.CustomerID = " & Me.CustomerID
Set rs = db.OpenRecordset(strSQLquery, dbOpenDynaset)
If rs.BOF And rs.EOF Then
With rs
.AddNew
!CustomerID = Me.CustomerID
Form_frmRecordCard.CustomerID = Me.CustomerID
.Update
End With

Else
Form_frmRecordCard.CustomerID = Me.CustomerID
Form_frmRecordCard.Name = rs.Name
End If

rs.Close
Form_frmRecordCard.Visible = True

When I click on the customer form to open the record card form with a corresponding customer id, I get: "This property is read only and can't be set". I believe it is referring to Form_frmRecordCard.Name = rs.Name

There are also a couple of things I have noticed and wondered if anyone could explain.

I can't get the show method to work with the form. I am currently using visibile = true. Why is this, it doesn't seem to appear in the object browser?

Also, on the line Form_frmRecordCard.Name = rs.Name
the suggested code shows an exclamation mark between rs and name. The object browser will not recognise this and seems to want me to use a dot. I have tried it with both

Is this something to do with differences between versions of Access, and - if so - which version are the majority now using?

Am on a steep learning curve!

Thanks

 

[PHV]

You should avoid using reserved word for your fields/controls ...
Form_frmRecordCard.Controls("Name").Value = rs.Fields("Name").Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GhostWolf]

I think your problem is with "Me.CustomerID" - it references a control, instead of the control's value.

Assuming the control's a textbox, you could do it more easily with CustomerID.Text, (be sure to change all your other references to Me.CustomerID accordingly).

If tblRecordCard.CustomerID is a numeric field, your Where statement's alright. However, if it's a text field, you'll need to modify your Where statement slightly to avoid a new problem:
"WHERE tblRecordCard.CustomerID = '" & Me.CustomerID +"'"