Unable to Enter any data on a field in form

[KevinSlater]

Hello,

I have a Form named: "DATA-BARCODE_SCAN_HISTORY" with a few fields, in this form when you type a Barcode number into the entry field (or scan a barcode with a scanner) it should then display the relevant products details such as the description, units and other data providing the product has a record in the database whilst recording the time and date the product was scanned into a table. However im unable to do this, I believe its probably related to the query it uses named: "SCREEN-DATA-BARCODE_SCAN" not being able to amend.

It should compare the field "BARCODE SCANNED" with the field "COM_DATA_VALUE" to see if theres a record in the database and display the other records data in the form if a match is found.

Any suggestions on how to resolve this would be great?, Ive attached the database if anyone is willing to have a look for me.

Thanks, Kev

 

[DCrake]

Simple Software Solutions

First of all you have your form set not to allow edits so you cannot type in or scan a barcode in the field.

Next it is a bound control and the underlying query is a grouped query making it not updateable.

What you need to do is to make you bar code field an unbound field and on your after update event of the field set the form filter to equal the bar code scanned in - if it exists.

Code Master:http://www.icraftlimited.co.uk

 

[raward1941uk]

I noticed that you've used a "Totals" query; wouldn't a simple query have enabled you to gather all the information? I have had the same problem myself and I got round it using a simple query.

raward1941uk

 

[KevinSlater]

OK thanks all for your replies. im trying to change the query driving the form to be updatebale, ive removed the totals but but no luck so far makeing it updateable. Ive also changed the Barcode scanned field in the form to unbound, made the form to allow edits have the below code on the after update of the BARCODE_SCANNED Filed, this works and displays the product details if a barcode match is found.....

However Would like to update the SCANNED_DATE and SCANNED_TIME fields to the current date/time, due to the quey not being updatable i get a runtime 2448 error saying: "You cant assign a value to this object"

Private Sub BARCODE_SCANNED_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "COM_DATA_VALUE='" & Me.[BARCODE_SCANNED] & "'"

If Not rst.NoMatch Then
'/record was found
Me.Bookmark = rst.Bookmark
'/update Time and DAte
Me.SCANNED_DATE = Now()
Else
MsgBox Me.[BARCODE_SCANNED] & " was not found."
End If
End Sub

SQL on the query driving the form below:

SELECT [DATA-BARCODES_SCAN_HISTORY].SCANNED_DATE, [DATA-BARCODES_SCAN_HISTORY].SCANNED_TIME, [DATA-BARCODES_SCAN_HISTORY].BARCODE_SCANNED, BARCODES.COM_DATA_VALUE, BARCODES.[COM_DATA_VALUE (2)], BARCODES.[COM_DATA_VALUE (3)], [DATA-BARCODES_SCAN_HISTORY].BARCODE_MATCHED, BARCODES.STK_DESCRIPTION, BARCODES.STK_SALE_UNIT_MEASURE, BARCODES.STK_PART_TYPE, BARCODES.COM_STK_GROUP, BARCODES.COM_STK_GROUP_DESC, BARCODES.COM_DATA_KEY, [DATA-BARCODES_SCAN_HISTORY].STK_PART_CODE
FROM [DATA-BARCODES_SCAN_HISTORY] LEFT JOIN BARCODES ON ([DATA-BARCODES_SCAN_HISTORY].STK_PART_CODE = BARCODES.COM_DATA_KEY) AND ([DATA-BARCODES_SCAN_HISTORY].BARCODE_SCANNED = BARCODES.COM_DATA_VALUE);

 

[DCrake]

Simple Software Solutions

Kevin

After the barcode has been validated and the record has been found run a simple update query on the TABLE that contains the scan date and time based on the bar code. THEN set the record source to the underlying query. By attempting to update the uneditable underlying query you are getting the error - which is correct. But by updating the field in the table first then refreshing the query the correct date/time will be displayed on screen as the bound object.

Also you cannot pass a value to bound control on a form because as it suggests it is bound to the field in the table not the value on the form.


David

 

[KevinSlater]

Ok David,

I have created a simple query that updates the time based on the bar code entered. After the barcode has been validated on the form on the afterupdate of the unbound barcode entry field named "BARCODE SCANNED" i have the following code:

DoCmd.OpenQuery "QUERY-UPDATE_SCAN_HISTORY"
Forms![DATA-BARCODE_SCAN_HISTORY].Requery

In the "QUERY-UPDATE_SCAN_HISTORY" query the BARCODE_SCANNED field has the following update to criteria: [FORMS]![BARCODE_SCAN_HISTORY].[BARCODE_SCANNED]

However i get a runtime error: 3063 duplicate output destinantion, any suggestions why this could be?

 

[DCrake]

Simple Software Solutions

Lets make things alot simpler

We know the table we want to update
We know the field in the above tabe we want to update
We know the barcode which has been scanned
We know the date and time it was scanned

Therefore:
We can add all this information together and create an sql string


So... on the AfterUpdate of the barcode field we can place the following code

Dim sSQL as string

sSQL = "UPDATE [TableName] SET [TimeStampFieldName] =#" & Now() & "# WHERE [BarCodeFieldName]='" & Barcode & "'"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarings True

DoEvents

Me.Refresh

This will then update the table. Now when the underlying query is refreshed the date wil be shown accurately

 

[KevinSlater]

Hi,

Yes that sounds like a better idea, ive tried inserting that code but notthing seems to happen, the table is not updated and no errors are shown, this is the code i have:

Private Sub BARCODE_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "COM_DATA_VALUE='" & Me.[BARCODE] & "'"

If Not rst.NoMatch Then
'/record was found
Me.Bookmark = rst.Bookmark

'/update Date
Dim sSQL As String
sSQL = "UPDATE [DATA-BARCODES_SCAN_HISTORY] SET [SCANNED_DATE] =#" & Now() & "# WHERE [BARCODE_SCANNED]='" & BARCODE & "'"
DoCmd.RunSQL sSQL
DoEvents
Me.Refresh

'/No match found
Else
MsgBox Me.[BARCODE] & " failed to find a match in the database.", vbInformation, "No match found"
End If
End Sub

I removed the DoCmd.SetWarings false and DoCmd.SetWarings True lines as it did not like these - said: "Complile error, method or data member not found" ill keep trying but if you have any suggestions why its not working please let me know.

Ive tried changing the data type for the filed SCANNED_DATE in the table but this doesnt seem to help. BARCODE_SCANNED is the field in the table for the scanned barcode.

 

[DCrake]

Simple Software Solutions

Kevin

Is the DATA-BARCODES_SCAN_HISTORY as Query or a table?

If it is a query then you will still have the same problem

What I suggest you do is to put a Breakpoint on the following line

sSQL = "UPDATE [DATA-BARCODES_SCAN_HISTORY] SET [SCANNED_DATE] =#" & Now() & "# WHERE [BARCODE_SCANNED]='" & BARCODE & "'"

When the code halts over the line press F8 to progress it to the next line then press Ctrl+G to to open up the immediate window and type in ? sSQL

Forgive me if you know what to do here but I have to work on the theory you don't.

Press Return

This should return the full sql statement turning the vatiables into real values.

Next highlight this line and copy to clipboard (ctrl+c)

Switch to queries and select New query

Don't select a table or query from the popup, simply close it.
Next click on the SQL button (top left on the toolbar menu) and paste in the code (ctrl+p).

Switch to design mode and see if it throws out any syntactical errors, if not, try and run it to see if it works. if it does not you will be given a clearer indication as to the error.

If you want to use DAO.Recordsets I would suggest the following:

Dim bFlag as Boolean


Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "COM_DATA_VALUE='" & Me.BARCODE & "'"

If Not rst.NoMatch Then
'/record was found
bFlag = true
'/No match found
Else
bFlag = False
End If

rst.close
Set rst = Nothing

If bFlag = True Then

Call UpdateTimeStamp(Me.BarCode)
Else
MsgBox Me.[BARCODE] & " failed to find a match in the database.", vbInformation, "No match found"

Endif

End Sub


Private Sub UpdateTimeStamp(MyBarCode As String)

Dim Rs As DAO.RecordSet

Set Rs = CurrentDB.OpenRecordSet("Select * From THE_TABLE Where THE_FIELD ='" & MyBarCode & "'")

If Not Rs.EOF And Not Rs.BOF Then

Rs.Edit
Rs.(THE_FIELD) = Now()
Rs.Update

End If

Rs.Close
Set Rs. = Nothing

Exit Sub

David

 

[DCrake]

Simple Software Solutions

Kevin

Just taking another look at your database and the table DATA-BARCODES_SCAN_HISTORY contains many occurances of the scanned barcode ie not unique.

If this is supposed to be a list of all available barcodes then you must make the BARCODE_SCANNED a Primary Key. However if you are creating a history of every time a particular barcode was scanned then you need to make the Barcode, Date & Time fields a 3 part primary key. However why not change the scanned date field format to General Date (dd/mm/yyyy hh:nn:ss) then you will not need the scanned time field. Thats why I suggested using Now() as the value for the scanned date.

As it is when you do a FindFirst ( I prefer Seek, its faster) it will only update the first occurance of the barcode.

Just a thought

David

 

[DCrake]

Simple Software Solutions

Kevin

Ok

Again looking at the mdb I spotted the Barcodes table.

Logic suggests the first step is to validate the user input ie Is what the user typed in or scanned in a valid barcode?

if found in Barcodes table then it seems you want to keep a history of everytime it was scanned for some reason. You should be then appending the data to the scanned history table NOT updating. But you could easily update a field in your barcodes table to record the last scanned date/time when it validates the barcode.

To further understand you could you give me a bit more of the meothodology and logic for the app.

David

 

[DCrake]

Simple Software Solutions

Kevin

Had time to look at your database in greater detail.

Have a look at the amended mdb and see if this is what you were looking for. I have tried to simplify it for you and still give you the same results. The main issue was that you were trying to update instead of append as the quereies were not updateable. I have changed the field structure of the Scanned Barcodes table so if you intend to use this adaptation make sure you take this into consideration with the rest of your app.


Best of Luck

David

PS if you require further help visit my website for more info.

http://www.icraftlimited.co.uk

 

[KevinSlater]

Hi David,

Thanks for all your help, yes that is exactly what i was looking for, it seems the only table structure you modified is the table: "EXT-COM_EXTRA_DATA_FIELDS" with the two fields: "AUDIT_CONSOLE_ID" and "AUDIT_LOGONID" - You changed these 2 fields to indexed- Yes (Duplicates OK). Is this correct?.

This doesnt work in my live database - a valid barcode is not Recognised in the form ie no data is displayed and the no barcode found message is shown at the bottom. Although the barcodes entered data is still copied over to the Barcode history table (with the barcode matched check box unticked) , so although this table is exactly the same structure/data before you modified it is a linked external oracle table and the structure cannot be modified so ive looked at all the code but im not sure how to get around this issue/or adapt it (the "EXT-STK_PARTS" and "EXT-COM_STOCK_GROUPS" are also external oracle tables that cant be modified"). I cant see why the AUDIT_CONSOLE_ID" and "AUDIT_LOGONID" field structures needed to be modified but i guess it needed to be done for this to work? please could you clarify why you made this change?.

Sorry about the further question above, i know youve already been really helpful & you said see your website for futher help, but if your able to advise on this last little hurdle it would be much appreciated.

Kev

 

[DCrake]

Simple Software Solutions

Kevin

The two amendments you referred to:

"EXT-COM_EXTRA_DATA_FIELDS" with the two fields: "AUDIT_CONSOLE_ID" and "AUDIT_LOGONID"

Were not changed by me

The only thing I changed was:

1. deleted the Scanned time form the history table
2. changed the format of the scanned date to General Date
2.1 changed the default value for new records to Now()
3. allowed zero lenght in the part code field
4. removed the scanned time from the main query

As the history table and its contents belong to you NOT Oracle you should be all right with these changes.

If this is a linked oracle table and you cannot change the format then

amend the following code

In the Function AppendTimeStamp

add the following lines within the Rs.AddNew section
Rs("Scanned_Date") = Date()
rs("Scanned_Time") = Time()

If you aren ot allowed to change the allow zero lenght property to the stk part code then change

Rs("Stk_Data_Code") = Nz(StkPartCode," ")

Finally
As you may have detected your form does not have to rely on a record source as all the fields are unbound so you only need to use your Barcodes query as a means of a lookup to validate the existance of the barcode. All other queries may be required in other sections of your app but are not used during the operation we have come to love and know.

As a thought for today...

What would they have called William the Conquerer if he had lost?

David

 

[KevinSlater]

Ok ive made an inteteresting discovery it all works fine if the table: "EXT-COM_EXTRA_DATA_FIELDS" is imported from Oracle. However if the same table is linked from Oracle it does not work....whenever you type in a valid barcode into the form it does not find a match and the no barcode found message is shown at the bottom. Although the barcodes entered data is still copied over to the Barcode history table (with the barcode matched check box unticked).

Its strange, perhaps the code is trying to search for a valid barcode to quickly on the linked version of the table, the table contains a lot of records (52116 records). The history table is not linked to Oracle - its part of this database only.

I even tried using exactly the same name for the Oracle table when its linked, also tried linking the "EXT-COM_EXTRA_DATA_FIELDS" table and removing the old non linked version directly into the database you modified but still does not work. its so odd, seems to be related to just this table. Any suggestions?

 

[DCrake]

Simple Software Solutions

Kevin

Morning.

The only thing that springs to mind is that when it is running the IsValidBarcode Function is that you may need to put a Trim() around the Where Condition

Before

Set Rs = CurrentDb.OpenRecordset("Select * from Barcodes Where COM_DATA_VALUE='" & MyBarCode & "'")


After

Set Rs = CurrentDb.OpenRecordset("Select * from Barcodes Where Trim(COM_DATA_VALUE)='" & MyBarCode & "'")


The fact that it is appending unknown scans to the scanned history table is defined you. If you do not want invalid scans being appended then amend the code accordingly.

Another test to see if the linked Oracle table is working correctly is to cut and paste a known barcode into the query condition and run it to see if it finds the record. If it does not then there is something funny going on. Could be that Oracle is the source of the problem and not Access.

Good Hunting

David

 

[KevinSlater]

Hi David,

I made the above change as you suggested and it now works fine

I dont really see why adding the trim function has made such a difference?, the trim function removes leading and trailing spaces from a string right?. its stange how it worked before when the oracle table was just imported into access but didnt when the table was linked but happy it works now.

Ill probably leave it so that unknown scans are still recorded in the history table as this isnt a problem. I'll read through your code again soon to ensure i can understand what its all doing and will probably add some comments.

Many thanks for all your help!.

Kevin

 

[DCrake]

Simple Software Solutions

Kevin

When the Where condition is looking for a matching record it is looking for an exact match, so any leading/trailing spaces are taken into consideration. When Oracle presents the data from a field it pads out any trailing spaces to the length of the field, SQL does the same. Access does not. So that is why no match was found. But if you trim both the criterion then a more accurate search is perfomed.

Glad evrything works now and hope this will enable you progress with any future development.

Warm Regards
David

 

[KevinSlater]

Thanks for Clarifying that for me seems like a good idea to use the Trim in any future checking/matching data in access more accurately especially when Oracle tables are involved. Yes ill bare in mind the great help youve given me here with any future Access work.

Thanks

Kevin