Making Dispatch Notes

[harrytgs]

Sorry rpeare, do you mind doing this on Access for me??



Would really appreciate :P

[rpeare]

Harry you need to learn how to replace the code, otherwise you won't know how to fix anything.

Just go to the ON EXIT property of the BRANCHID field, you will see a bunch of code there, if you compare it to the new code I wrote on this my last post you will see it's slightly different (modifies a different field) if you replace the existing code with the code I've written it should work fine.

The same thing goes for the TAG property of the bransomref field, just open the properties window and go to the TAG property it should say DE (click on any of the other fields on the screen and go to the TAG property and you'll see what I mean.

[harrytgs]

I am now getting a Run-time error '3464' - Data type mismatch in criteria expression.

I am assuming this is because the lookup is on shop names, however the validation / record source is the branch ID.

Also, why is the code talking about the bransom ID? The bransom ID is from the shops, the dispatch note is the WSHOP number ??

[rpeare]

Check to make sure all of the ID fields are listed as either autonumber on their own table, or number on any foreign table.

[harrytgs]

There are only two tables, and only ID is on branches - ID is autonumber and the ID on the bookings table is Text, and I can't change this because of the relationship.

[rpeare]

remove the relationship, change the data type to number, then re-establish the relationship (note, it's not necessary to establish relationships in your databases, it can just smooth things out when it comes time to build queries etc)

[harrytgs]

Now run-time error '13', Type Mismatch. Again, assuming this is because the lookup is on shop names, however the validation / record source is the branch ID.

[harrytgs]

Also, when trying to update, I click on a record at the top and I get a run-time error '3021', No Current Record.

[rpeare]

The lookup is looking at column 1, the second column (column counting starts at 0) so that should not be the problem. Not sure what you mean by the second thing

[harrytgs]

I have had a look at the properties, it seems that the source is the BranchID (1,2,etc) but the dropdown (lookup) is for the Branch name (London,Holloway,etc) hence the problem...

Not entirely sure how to change it without problems...

[rpeare]

The source of that dropdown box should be a query based on the locations table with the ID being the first field, the location text being the second field and the bound column being the first field with the width of that column set to 0 so all you see when you're doing data entry is the location description.

[harrytgs]

Would you mind checking the database to confirm please?

[rpeare]

Post an updated database in access 2003 or below format. I can't read 2007 files most of the week.

[harrytgs]

I have attached.

Thanks

[rpeare]

1. You changed the primary key field of your TBL bookings but your list box no longer reflects that. Go to PROPERTIES window of the list box Lst_BOOKINGS. Change the SQL (ROW SOURCE) statement running this list box to:

SELECT Bookings.[Bransom Ref], Bookings.Ref, Bookings.BranchID, Bookings.[Date Received], Bookings.Description, Bookings.[Date Dispatched], Bookings.[Dispatched By] FROM Bookings ORDER BY Bookings.[Date Received] DESC;

Change the column count to 7.
Change the column widths to 0";1";1";1";1";1";0.5"

NOTE: If you want the bransom ref field to show in the list box make the first field have a width larger than 0

2. You didn't change the data source for the location combo box so that it stored the locationID. The ROW SOURCE property should be this query:

SELECT Branches.BranchID, Branches.Branch
FROM Branches
ORDER BY Branches.Branch;

3. You didn't change any of the code I told you to in the ON EXIT property of the location combo box.

Replace the existing code in that event with the code listed 10 or 15 posts ago

Dim sMaxRef
 
sMaxRef = DMax("[Bransom Ref]", "Bookings", "([BranchID]) = " & Me.BranchID)
 
If Not IsNull(BranchID) Then
    If IsNull(sMaxRef) Then
        Bransomref = UCase(Left(BranchID.Column(1), 2)) & "-0000001"
    Else
        Bransomref = UCase(Left(BranchID.Column(1), 2)) & "-" & Right("000000" & CLng(Right(sMaxRef, Len(sMaxRef) - 3)) + 1, 6)
    End If
End If

4. You didn't change the TAG property of the BRANSOMREF field to DE, if you don't do this that field will not be cleared out when new records are added.

CHANGE THE TAG PROPERTY OF BRANSOM REF TO THE TEXT STRING:

DE

5. Change the ON CLICK event of your list box (LST_BOOKINGS) to the following

Dim db As Database
Dim rst As Recordset
Dim ssql As String

Set db = CurrentDb
ssql = "SELECT * FROM Bookings WHERE (([Bookings]![BRANSOM REF]) = '" & Lst_Bookings & "')"
Set rst = db.OpenRecordset(ssql)
rst.MoveFirst

Me.Ref = rst.Fields("ref")
Me.BranchID = rst.Fields("branchid")
Me.DateReceived = rst.Fields("date received")
Me.Description = rst.Fields("description")
Me.DateDispatched = rst.Fields("date dispatched")
Me.DispatchedBy = rst.Fields("dispatched by")
Me.Bransomref = rst.Fields("bransom ref")

rst.Close
Set db = Nothing
DoCmd.GoToControl ("BranchID")