Add/Update Subform VBA code

[jal]

Here's a possibility

"..WHERE Booking_ID = " & bookingID & " AND Type LIKE 'Price' "

This assumes that bookingID is numeric. If it is TEXT type:

"..WHERE Booking_ID = '" & bookingID & "' AND Type LIKE 'Price' "

 

[jal]

How many tables are involved here? I hope just one, called tblCustomers? The query won't work if "booking_ID" is in another table.

 

[jal]

Also I think this suggestion:

WHERE [booking_id] = Forms!frmdetails!booking_ID

was another error on my part because I think this syntax only works from a DAO.QueryDef object (a query in the object pane) and thus not from ADODB. Sorry about that.

 

[robyholmes]

Ok, here is more information.

I have a few tables, the ones needed for this are:

tblbooking
tblpayment

tblpayment has a foreign key of booking_id from tblbooking. So I'm searching for that and type, which are both in the tblpayment.

tblpayment is:

payment_id : booking_id : amount : account_no : sort_code : type : received : sage : sage_date

I just want to check booking_id is same as the one I am viewing in my details form, and that type = price. This means that the booking already has a price set. So i need to update it. If it doesn't return any then I need to add the price.

Make sense?

 

[robyholmes]

I see why you getting confused with table, I put tblCustomer in, LOL

Thats probs why

 

[robyholmes]

Ok, here is the code all checked.

SQL = "SELECT * FROM tblpayment WHERE [booking_id] = Forms!frmdetails!booking_ID AND [type] = 'Price' "
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

Same error as before.

Also, do I not need to set the booking ID in the second bit, so that it ties up with the tblbooking

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

IE the [amount] and [type] are set but not the booking_id? Could this be coursing the error? If you want the database to look at I will convert and send it to you.

 

[jal]

Go ahead and upload it although I'm not sure when I'll look at it. As I was saying, you probably cannot use this syntax in ADODB:

SQL = "SELECT * FROM tblpayment WHERE [booking_id] = Forms!frmdetails!booking_ID AND [type] = 'Price' "

See my corrections above. Or you could go back to DAO and do something like this:
Dim qDef as DAO.QueryDef
Set qDef = CurrentDb.CreateQueryDef("")
qDef.SQL = "SELECT * FROM tblpayment WHERE [booking_id] = Forms!frmdetails!booking_ID AND [type] = 'Price' "
Dim rs as DaO.Recordset
set rs = qdef.OpenRecordset
rs.Edit
rs("Amount") = amount
rs.Update
rs.close
set rs = nothing

 

[robyholmes]

I can't convert it, well access says it can't. I don't no why, cos I can't remember using any 07 only features. O well.

I will give this code a go now. So you don't think its that I havn't set the booking_ID when adding a new one?

 

[robyholmes]

I have put your code in now, and getting this error:

Too few parameters. Expected 1

Here is the full code:

Dim bookingid As String
txtprice.SetFocus
amount = txtprice

Dim qDef As DaO.QueryDef
Set qDef = CurrentDb.CreateQueryDef("")
qDef.SQL = "SELECT * FROM tblpayment WHERE [booking_id] = Forms!frmdetails!booking_ID AND [type] = 'Price' "
Dim rs As DaO.Recordset
Set rs = qDef.OpenRecordset

'Checking for price for booking and updating
If rs.RecordCount > 0 Then
rs.Edit
rs("Amount") = amount
rs.Update
rs.close
Set rs = Nothing
Else
Set frm = Me.frmpayments.Form
    With frm.RecordsetClone
    .AddNew
    ![booking_id] = Forms!frmdetails!booking_id
    ![amount] = amount
    ![type] = "Price"
    .Update
    End With
    Set frm = Nothing
End If
rs.close
Set rs = Nothing

 

[jal]

I apologize. I can't seem to get this right. I guess that syntax only works in queries run from the object pane, not from VBA. Try it this way

Dim qDef As DaO.QueryDef
Set qDef = CurrentDb.CreateQueryDef("")
qDef.SQL = "SELECT * FROM tblpayment WHERE [booking_id] = @booking_ID AND [type] = 'Price' "
qdef.Parameters.Refresh
qdef.Parameters("@booking_ID").Value = booking_ID.Value
Dim rs As DaO.Recordset
Set rs = qDef.OpenRecordset

This should work, assuming your table tblPayment does in fact have the two columns 'booking_id' and 'Type'.

 

[robyholmes]

HOLY COW! It works! Well, ish.

It will update the price, but if there isn't a price set then it won't bring information from customer and booking, it needs to have a record with the booking_id in the payments table? Why do you think this is?

The payments tab has a sub form, with the payment table, it is linked to the booking_id, do you think this is making it only show records with a booking ID in the payments table?

Seems odd, it should just show a empty table right?

Thanks a lot for your help. You have been a BIG help too. Now I just have to work out this little problem, then about another 500, lol.

 

[jal]

I don't understand. You seem be saying that the info is there and NOT there. Which is it?

It will update the price, but if there isn't a price set then it won't bring information from customer and booking (so it is NOT there)? it needs to have a record with the booking_id in the payments table? Why do you think this is?

The payments tab has a sub form, with the payment table, it is linked to the booking_id, do you think this is making it only show records with a booking ID in the payments table?

Seems odd, it should just show a empty table right? (so it IS there?)

 

[robyholmes]

Ok, If there isn't a record in the payment table, it will not show the booking. So if you want to view a booking where the price is not set, it will not show in the details form.

If there is a record in the payments table matching the booking_ID then it will show all the booking info in the details form, the script will then update the price or add a new one.

Can you see what I am saying?

 

[jal]

Your complaint only sounds rhetorical. If there is no such record in table payments, how can table payments display the record?

I can help you display existing records. I cannot help you display records that don't exist as yet.

If you need to list all the bookings as to make them visible on the payments subform, then it seems to me you will need to display the bookings table on the payments tab.

 

[robyholmes]

Ok, I have 3 tabs, and 3 tables here.

Customer: tblcustomer
Booking: tblbooking
Payments: tblpayment

The problem is that if a customer has a booking, say booking_ID = 3, then 3 must be in tblpayments for it to open in the details form.

If I have a booking_ID with no payments made, and no price set, then it won't show in the details form?

 

[jal]

I don't understand where you are confused. You have a tab that shows the bookings table. If there are records in that table, they will, of course, display on that tab.

You also have a payments tab showing the records from the payments table. Those records previously added to this payments table will, of course, display. Any records which have NOT been added will not, of course, display.

In sum, you can only display records that exist. Where then is the confusion?

If you need to show a booking_Id that has not yet been added to the payments table, you must either (1) add it to the paymnents table or (2) display the bookings table.

 

[jal]

Oh sorry, I think I misread your last post, I'll read it again.

 

[jal]

The problem is that if a customer has a booking, say booking_ID = 3, then 3 must be in tblpayments for it to open in the details form.

If booking "3" is not yet in the payments table, then, yes, you must first add it to the payments table if you want to display it on the payments subform. Or, display the whole bookings table next to the payments subform. However you like.

 

[robyholmes]

But If I can a customer without a booking they will show in the details panel before the payments was put in.

If there is a customer without a booking there details can still be seen in the details form? I don't understand why a booking need to have a payment for it to show its details?

Its really hard to explain I know, but something is wrong here.

 

[robyholmes]

Ok, let me try and explain it best I can

I have 3 tabs:
Customer | Booking | Payments

I want all records to show in this form. So if there is a customer without a booking, they still show, so that I can add a booking. If a customer has a booking but no payments or price has been set, it needs to show. At the moment, it will only display customer's and there bookings if there is a payment in tblpayments with that booking_ID