Add/Update Subform VBA code

[robyholmes]

Hi, I have made a 'Details' pop up form which I will use to show the details of customers and there holiday bookings in my database.

I have a 'Bookings' tab, this contains a unbound text box called 'txtprice' This is the price of the holiday. I then have another tab 'Payments'. This tab contains a sub form with 'things like 'amount, type, account number'

What I want is for people to type the price of the holiday in to the 'Booking' tab 'txtprice' field and that add a new record to the subform in payments with amount been the price but negative (So they are in debt)

I got it to do this with the following code:

txtprice.SetFocus
amount = txtprice

Set frm = Me.frmpayments.Form
With frm.RecordsetClone
.AddNew
![amount] = amount
![type] = "Price"
.Update
End With
Set frm = Nothing
adults.SetFocus

At the mo this doesn't make it negative I know
The problem is that this adds a new price every time, I need it to check if there is already and record with 'type = price' and update the amount.

I am learning VBA, must of the stuff (Like the code above) is form the internet, so explaining each bit would be great!

Thanks a lot
Rob Holmes

 

[robyholmes]

:BUMP:

I would still like the code of this. I have tried other sites and no look!

Thanks
Rob Holmes

 

[DCrake]

Tip. To turn a positive into a negative and vice versa use the following formula

Balance = (Price * -1)

100 becomes - 100

-100 becomes 100

You could also use Balance = (0 - Price) but this is only single sided.

David

 

[robyholmes]

Thanks, but I really need to code to check if the price/amount is aready in? And if so update it.

 

[jal]

Maybe something like this:

Dim rs As New ADODB.Recordset
Dim SQL as string
SQL = "SELECT * FROM myTable WHERE [Type] LIKE 'Price' "
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
if rs.RecordCount > 0 Then
rs("Amount") = amount
rs.Update
Else: 'your other code here
End if
rs.close
set rs = nothing

 

[robyholmes]

Maybe something like this:

Dim rs As New ADODB.Recordset
Dim SQL as string
SQL = "SELECT * FROM myTable WHERE [Type] LIKE 'Price' "
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
if rs.RecordCount > 0 Then
rs("Amount") = amount
rs.Update
Else: 'your other code here
End if
rs.close
set rs = nothing

So as this is on a form, I would have to include a WHERE for the booking_id? As this would only display payments for that booking.

So

SQL = "SELECT * FROM myTable WHERE [booking_id] = booking_id & [Type] LIKE 'Price' "

I have problem got the snytax wrong there, so please correct me. I will be trying this tomorrow hopefully. Thanks a lot

 

[jal]

You're probably on the right track but use the word "AND"

SQL = "SELECT * FROM myTable WHERE [booking_id] = booking_id AND [Type] LIKE 'Price' "

Also put in the table name, say Customers

SQL = "SELECT * FROM Customers WHERE [booking_id] = booking_id AND [Type] LIKE 'Price' "

 

[robyholmes]

Ok, I finally got round to it this morning. And got 'User defined type not allowed' on 'rs As New ADODB.Recordset'

Here is the code:

Private Sub txtprice_LostFocus()
Dim bookingid As Integer
booking_ID.SetFocus
bookingid = booking_ID
txtprice.SetFocus
amount = txtprice

Dim rs As New ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM tblCustomers WHERE [booking_id] = bookingid AND [type] LIKE 'Price' "
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
    rs("Amount") = amount
    rs.Update
Else:
    Set frm = Me.frmpayments.Form
    With frm.RecordsetClone
    .AddNew
    ![amount] = amount
    ![type] = "Price"
    .Update
    End With
    Set frm = Nothing
End If
rs.Close
Set rs = Nothing
adults.SetFocus

End Sub

Can you see why?

 

[jal]

if you are using Access 2007, you might want to add a reference to ActiveX Data Objects 2.8 Object Model.

 

[robyholmes]

if you are using Access 2007, you might want to add a reference to ActiveX Data Objects 2.8 Object Model.

Yes I am using 2007, how do I add a 'ActiveX Data Objects 2.8 Object Model'?

 

[jal]

This is the library for ADO (which includes ADODB.Recordsets). Add it at Tools > References from the code window. By the way, I see an error I made, "adLockReadonly" - you don't want readOnly if you are updating so you ue adLockOptimistic.

Or you can use a DAO recordset instead, which performs faster in large applications.

Dim rs As DAO.Recordset
set rs = CurrentDb.OpenRecordset(SQL)
If rs.RecordCount > 0 Then
rs.Edit
rs("Amount") = amount
rs.Update
Else...

 

[robyholmes]

Ok, I have added that and sorted my table name (I put tblcustomers not tblcusomter)

Now I have this problem:
No value given for one or more required parameters

At a guess this is saying that the record set doesn't have on of the WHERE commands. I will post all my code below. I did have a problem with bookingid, as Integer was to short, put string in now, is this wrong? I no string is for text.

Private Sub txtprice_LostFocus()
Dim bookingid As String
booking_ID.SetFocus
bookingid = booking_ID
txtprice.SetFocus
amount = txtprice

Dim rs As New ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM tblCustomer WHERE [booking_id] = bookingid AND [type] LIKE 'Price' "
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rs.RecordCount > 0 Then
    rs("Amount") = amount
    rs.Update
Else:
    Set frm = Me.frmpayments.Form
    With frm.RecordsetClone
    .AddNew
    ![amount] = amount
    ![type] = "Price"
    .Update
    End With
    Set frm = Nothing
End If
rs.Close
Set rs = Nothing
adults.SetFocus

End Sub

 

[jal]

I'm sorry, I didn't think that through. If bookingID is a field or textbox on Form1, you will probably need something like this:

"SELECT * FROM tblCustomer WHERE [booking_id] = Forms!Form1!bookingID AND [type] LIKE 'Price' "

 

[robyholmes]

I'm sorry, I didn't think that through. If bookingID is a field or textbox on Form1, you will probably need something like this:

"SELECT * FROM tblCustomer WHERE [booking_id] = Forms!Form1!bookingID AND [type] LIKE 'Price' "

Well I had set the bookinging_id (The field) as a variable, but I will try this tomorrow, if I can a chance thanks

 

[robyholmes]

Still same problem.

Its says its on this line

rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

Here is all the code again.

Private Sub txtprice_LostFocus()
Dim bookingid As String
txtprice.SetFocus
amount = txtprice

Dim rs As New ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM tblCustomer WHERE [booking_id] = Forms!frmdetails!booking_ID AND [type] LIKE 'Price' "
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rs.RecordCount > 0 Then
    rs("Amount") = amount
    rs.Update
Else:
    Set frm = Me.frmpayments.Form
    With frm.RecordsetClone
    .AddNew
    ![amount] = amount
    ![type] = "Price"
    .Update
    End With
    Set frm = Nothing
End If
rs.Close
Set rs = Nothing
adults.SetFocus

End Sub

I'm really suck with this. If you want me to upload the file I can. I will PM you details of download.

Also the form will only show customers with payments? Should it do this. Cos I don't want it to. The form will be opened with filters usally. Dude I'm racking my brains with this one. Lol

Thanks for all your help

 

[jal]

I might have some time to look at this, but I'm running Access 2003. You would have to convert it down to an MDB file. I know there's a way to do so but I don't know what it is.

 

[jal]

I am surprised you're still getting that error. Let's be clear.

WHERE [booking_id] = Forms!frmdetails!booking_ID

The word in red should be the exact name of the column as given in table design view (and I hope you aren't using lookup columns). The word in purple should be the name of the textbox on the form. By default these two names are the same on a bound form unless you changed the textbox name. I also assumed the table has a column called "Type".


AND [type] LIKE 'Price'

 

[robyholmes]

I am surprised you're still getting that error. Let's be clear.

WHERE [booking_id] = Forms!frmdetails!booking_ID

The word in red should be the exact name of the column as given in table design view (and I hope you aren't using lookup columns). The word in purple should be the name of the textbox on the form. By default these two names are the same on a bound form unless you changed the textbox name. I also assumed the table has a column called "Type".


AND [type] LIKE 'Price'

Ok, the txtbox is bound to booking_ID. The price is a lookup, so do I have to use the ID? Its not looking up in a table tho. Should I get rid of the lookup, then use a unbound combo box or group to select the type in the payments tab. This would make sense as I only want 'Deposit' and 'Balence' in there.

I will check it all over again today and get rid of the lookup.

Thanks

 

[jal]

I don't know how lookups work. I hear they cause problems and confusion so I would say Yes, get rid of it.

 

[jal]

Actually, I'm not sure what you mean:

The price is a lookup, so do I have to use the ID? Its not looking up in a table tho.

If the column called "Type" contains or can contain the word 'Price' Then that part of the query should be fine.