Ok, this seems like such a basic need that the answer can't be that complicated. Basically, I have a form that is a customer billing form in a reservations DB. There are two buttons. One is "Edit reservations" and the other is "Add reservations". However, if the customer is newly created, then the "Edit reservations" button should stop and, through a message box, tell the user that no reservations have been made and that they should first use the "Add reservations" button. But if the customer already has a reservation that he wants to add to, the "Edit reservation" button should take him to his reservation record in the form.

my code looks like this:

Private Sub editResBut_Click()
On Error GoTo Err_editResBut_Click

Dim stDocName As String
Dim stID As String
Dim stLinkCriteria As String
Dim strSQL As String

stDocName = "CurReservParent"
stID = Me![CustomerID]
stLinkCriteria = "[CustomerID]=" & stID

strSQL = "SELECT [Reservations] WHERE " & stLinkCriteria & ";"

DoCmd.RunSQL strSQL

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_editResBut_Click:
Exit Sub

Err_editResBut_Click:
MsgBox "Reservation has not yet been made. Please 'Add Reservation' first."


Resume Exit_editResBut_Click

End Sub


The reason I run the SQL first is that the line: DoCmd.OpenForm stDocName, , , stLinkCriteria will open an existing record based on the criteria, but if there is no existing record, it doesn't go to a new autonumbered record, but instead to one with an ID of 0. So I was hoping the SQL would trap on the select if there was no record and otherwise it would open the existing form. But instead, it traps no matter what.

HELP ME, PLEASE!!

Thank you for any assistance.

Ian

Please?

Ian,

I think the function DCount would come in handy here.

Try this:

Private Sub editResBut_Click()
On Error GoTo Err_editResBut_Click

Dim stDocName As String
Dim stLinkCriteria As String

TableTotal=
DCount ("*", "Reservations","[CustomerID]= & Me!stID") <all on one line>

If TableTotal <> 0 Then

stDocName = "CurReservParent"
stLinkCriteria = "[CustomerID]=" & Me!stID
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else
MsgBox "Reservation has not yet been made. Please 'Add Reservation' first."
End If

Exit_editResBut_Click:
Exit Sub

Err_editResBut_Click:
MsgBox Err.Description
Resume Exit_editResBut_Click

End Sub


Suc6,

RV

Thank you, thank you, thank you!!!

So frustrating, but thanks to your help, it worked. I am in your debt.

One other question, please:

I am having trouble on the form showing totals for all the charges for each room. I have 3 tables: Customers, Spaces, and Reservations.
Their relationships are defined thus: A customer can have many reservations (related through CustomerID), and each reservation can have many spaces (related through ReservationID).

Each space can be of five different types (and thus deposits), and can also be charged for cleaning, etc.

All of that works fine. I can add reservations (thanks to your help), add spaces to reservations, etc. I am working on a billing form now, and have need of a field with a total. I created a "Primary Query" that merely looked at all the SpaceIDs, charges, CustomerID's and ReservationIDs. Then I created a "Secondary Query" based on it that summed all those individual fields (as well as including unique customer information, like AmountPaid, BillingAddress, etc). I based a form on that 2nd query, made a totals textbox and set it's source to the sum of all those individual sums (minus AmountPaid). It works- showing the total for all reservations/spaces for a specific CustomerID. But there are two more fields I need reference to (startdate and enddate- they are part of the Reservations table) to calculate the number of total days to use in the 'total' calculation. But since the form is based on that query, if I simply set text boxes with their source as those two fields, it wont let me do it. It says "#Name?" in those text boxes. How can I reference those fields only for the reservationID's connected to that customer. (that was why I included all that extraneous info in the secondary query- I couldn't reference it in a form based on a query without them. I didn't want to go back and include ANOTHER table in the query just to get access to one field.) It just seems like poor db design. It doesn't seem to follow object oriented principles.

Any suggestions?

Thank you again.

Ian

Ian,

Perhaps you can add a field to your second query to calculate the number of total days
in your second query.
Otherwise, post the SQL statement of your second query as well as the source of the two textboxes for startdate and enddate you're referring to in your billing form.

Greetings,

RV

This is where I have problems. I know how to get the SQL for the queries I make, but what does it mean to post them. How to I use SQL is this case (or more generally for that matter) in both VBA (which I've done very limitedly and primarily through trial and error) and in places like Property items?

Thank you for your help. I do appreciate it.

Ian

Ian,

The textboxes you add to your billing form should refer to fields in the query the form is based on.
I guess "startdate" and "enddate" are no fields in your query you use for the billing form.
That could be the reason why you get an error #Name.

It's a guess, that's why I asked to post your SQL.

Greetings,

RV

Fool that I am, I didn't even notice the "post" remark in your message. UBt it's ok. I've gotten it taken care of with what you've suggested. Now I having trouble with a Runtime error 3061, and the research I've done at this site leads me to believe that this happens when an item you're referring to isn't being reffered to consistantly in the same way (spelling, caps). But my problem is that what I'm doing works in the first method it appears, but when I copy it (and change all the relevant field information to that particular method, I get an error.)

Here's the original:

Private Sub amtPdEdit_AfterUpdate()
Dim strSQL As String
Dim CustID As String
Dim stamtPdEdit As String

stamtPdEdit = Me![amtPdEdit]
CustID = Me![CustomerID]

strSQL = "UPDATE [Customers] SET [Customers].[AmountPaid] = " & stamtPdEdit & " WHERE " & CustID & " =[Customers].[CustomerID];"
CurrentDb.Execute strSQL
DoCmd.Requery
Me![amtPdEdit] = Me![AmountPaidTextBox]
End Sub


And here's how it appears in a similar method (on the same form but reffering to different fields and textboxes)


Private Sub Notes_AfterUpdate()
Dim stNotes As String
Dim CustID As String
Dim strSQL As String


stNotes = Me![Notes]

CustID = Me![CustomerID]

strSQL = "UPDATE [Customers] SET [Customers].[Notes] = " & stNotes & " WHERE " & CustID & " =[Customers].[CustomerID];"
CurrentDb.Execute strSQL
DoCmd.Requery
End Sub


I used Execute to get around the warning about changing a record's field. I've triple checked the names of fields in the table [Customers], as well as the names of the text boxes themselves.

Unless...the 'Notes' text box is just that, a text box. But the field I am writing to is actually a "Notes" field. Could it be a typecast conversion error? If so, how can I cast that "Notes" text box to a Memo object?
(I am having the same problem with a list box writing a selected value to a "text" object.)

Thanks

Ian