booking in and and booking out form help

[munkeyroot]

Hi all

hope you can help i have created a few form databases but am a bit stuck on this one

i am designing a booking in booking out system

quick layout of what i like to acheive

booking in

a load comes in and an open weighbridge ticket gets created via another programme and given a ticket number.

load gets booked in a access booking in form and items are given a separate unique barcode which then gets scanned in the booking in form against the open weighbridge number.

booking out

an new open weighbridge ticket is generated with ticket number via another programme.

items to be booked out are barcode scanned and added to a list against the opened weighbridge ticket.

when booking out process is complete, the booked out access list gets printed and signed off by the weighbridge and driver.

quiery on how to achiecve this.
would it be a case of playing around with the relationships? if so which way would be best?
would the booking out list be better on access geneated speadsheet or could this be done in anotherway?

as you can see i am a little lost haha

again hope you can help
cheers matt

 

[CBrighton]

Are the two sets of data related?

Will loads / items which have been booked in later be booked out (or visa versa) or are they two completely separate sets of data?

 

[Steve R.]

You may want to look at the sample models provided here: Database Answers

 

[munkeyroot]

to cbrighton

Hi mate thank you for your post.
yes the items booked in will be booked out at a later date as a customer will purchase them.

i have to log items booked in that come in to goods in against the weighbridge ticket, supplier and date.

then when a new customer purchases these items they will get booked out on a new weighbridge ticket against the date purchased and customer.

i am unsure whether to have a "booking in" form of this discription

date [manually entered]
supplier [drop down]
weighbridge Number [manually entered]

then a form spreadsheet within the same form containing items booked in via barcode.

the fields could be (red = manually entered)
barcode | item type discription | our own numbered tag | pat test no | pat test date

then a same kinda form for booking out, just change the supplier to customer, and they scan the previosly "booked in" items out to the cutomers weighbridge number.

when the barcode gets scanned into the booking out form, is it possible to automatically get access to pull the rest of the data from booking in.

i hope i'm on the right track...i think...

cheers matt

 

[munkeyroot]

to Steve R

Thanks mate i'll have a look, thankyou for the link

matt

 

[CBrighton]

If I were building this database I would go one of two ways, however in both cases the general structure would be the same.

One table with orders, second table with items. Link them on the ticket number generated when booking in.

I would then decide between:

1. Having firelds in the order table like "BookedOut" (bool), "TicketIn" & "TicketOut", etc to allow me to have just one set of data and queries based on the BookedOut field would let me identify which orders have shipped.

2. Having two sets of orders and items tables, one for orders/items received & one for orders/items sent. An append query & delete query would then allow me to transfer received orders to sent orders when the order is sent.

Form-wise I would have the order form with the item subform embedded within it.

 

[munkeyroot]

To cbrighton

Hi mate, oh thanks mate for heads up thats great i'm getting a better idea on how to do this.

thank you for your help.

p.s just a quick one with barcode reader i'm finding when i scan into a cell it enters across to the next cell, is there some code or setting in access that will let me go to a new row?

cheers matt

 

[CBrighton]

I'm afraid barcode scanners are a bit beyond me, never used one.

 

[munkeyroot]

Hi mate ok pal no problem thank you for getting back to me.

sorry to keep picking your brain pal, when i scan in a barcode how can i get the barcode to pull the info from another table?

matt

 

[CBrighton]

I don't know what happens when you scan the barcode.

What data is captured (are we talking about a single numeric value which is generated based on the barcode or are there various fields of info automatically populated based on the barcode) and where does it get populated (is there a table which it is pulled into when you scan a barcode)?

Without knowing what happens when you scan a barcode it's hard to say how you would link that data.

 

[munkeyroot]

hi mate ok sorry to bother you,

ok barcode is scanned, item type, manufacture is inputted manually.

on different form
barcode is scanned and the item type and manufacturer is autofilled when barcode is scanned

hope this helps

 

[CBrighton]

Let's stick with the initial form for now, the one where you are inputting item data to go with the barcode which was just scanned.

What actually happens when the barcode is scanned?

Does it populate a field in the form or create a record in the underlying table? Or does it copy a number into the Windows clipboard which the user then manually pastes into the barcode field?

 

[munkeyroot]

ok cool,

the user clicks in the "barcode field" and then the barcode is scanned. The barcode numbers automatically fill the field and a new record is created.

the user manually inputs ITEM TYPE | MANUFACTURER

The data is just stored in the table

 

[CBrighton]

munky,

Could you upload an example database (you can, and should, remove any live data before uploading. However dummy data showing the format of the data would be useful)?

I could do with seeing what happens in the On_Click event of the barcode field.

Feel free to remove any forms, reports, etc which are not related to this part of the process.

And you will need 1 more post before you can attach, it requires 10 posts. As a final note, please save it as an mdb compatible with Access 2003 or I won't be able to open it.

 

[munkeyroot]

hi mate

right ok cool thats no problem, i'm working in 2003 anyway mate. i had a play around with the relationships but thats did work lol which im not supprised ha.

no worries i'll upload in a tick.

matt

 

[munkeyroot]

hi mate

Right process i've got so far

step 1
Form:- Goods in

User inputs date,supplier [dd],weighbridge no.
selects Barcode subfield an scans in barcodes

step 2
Form:- Refurb
user locatcates record by find button and scans in barcode
user fills manually fills in item type, manufacturer etc

step 3
Form:- Goods out
user inputs date, customer, weighbridge no.
user scans in barcodes and the item and manufacturer (will be automatialy populated from table BOOKIN DATA .... Well thats what i'm trying to do haha)

just a quick note
Goods in and goods out weighbridge numbers will be different.

hope you can follow, hee he its hard to discribe by typing is'nt it lol

well hope you can help
you've been a star mate.

matty

 

[CBrighton]

Still not 100% sure how the barcode is populated, was expecting something in the On_Click event which would show access requesting barcode data from the reader and populating it in the field.

Still, try adding the following in the VBA code for form_GOODS OUT DATA (or open that form, highlight the BARCODE text box, open the proerties window, select the Event tab and the After Update property, click the ... on the right hand side and select Code Builder) :

Private Sub BARCODE_AfterUpdate()
Dim db As Database
Set db = CurrentDb
Dim rst As Recordset
Set rst = db.OpenRecordset("SELECT * FROM [BOOKIN DATA] WHERE BARCODE = '" & BARCODE & "'")
If Not rst.EOF Then
    rst.MoveFirst
    [ITEM TYPE] = rst![ITEM TYPE]
    MANUFACTURER = rst!MANUFACTURER
End If
Set rst = Nothing
Set db = Nothing
End Sub

If I manually input an existing barcode into the field it populates the item type and manufacturer fields, but as I still cannot see how the barcode is populated I don't know if it will work for you.

This is a simple way to do it via VB with no need to setup relationships, queries, etc.

You can add additional an Else into the If function to tell the database how to react if the barcode is not found in the BOOKIN DATA table.

 

[munkeyroot]

Hey mate
ooohhh mate you are a star pal, thank you. i'll have a bash at the code.

yeah the barcode data is litterally just click scan bunch of numbers appear in the field and automatically enters across to next field
it works on a keyboard widgit apparently which just acts as a person typing really quick lol. that basically it.

thank you soooo much for your help mate

kind regards
matt

 

[CBrighton]

No problem.

That event will fire when focus moves out of that field after it has been changed, going by your description of the barcode scan process above it sounds like that should be fine as you said it automatically tabs to the next field.

If it doesn't work with your widget then come back here.

 

[munkeyroot]

Hey mate

Just to let you know all that work and.....it works ha ha awesome

cheers mate your a star

just a quick one when the scanner scans in the data from the barcode it just enters to the next field in the same record how do i get so that when its done what it needs to do so it automatically goes to the next record would i have to do something with ON_CLICK ?