No need for file now. Have a look at the IIf function: https://support.office.com/en-us/art...0-647539c764e3
Cheers,
Vlad
No need for file now. Have a look at the IIf function: https://support.office.com/en-us/art...0-647539c764e3
Cheers,
Vlad
So all of that works now.
Im trying to add a login form using this: http://accesshosting.com/create-login-form-ms-access
not quite working yet, not sure how to add the macro to the button. i already assigned it but its not functioning.
what should i do with the textboxes? assign them to the columns? wouldnt that make the form edit the name in the colum? i want to prevent that ofcourse.
Here it is. You changed the table name and field name (password from userpassword in the code). Please note that the code makes reference to a form frmUserInfo that you do not yet have (in that one you do want to bind the fields from tblUser to allow them to change their password).
On my site I have a free download that allows you to customize the look and functionality of an Access database for certain groups (levels) of users: http://forestbyte.com/ms-access-util...access-levels/. It might be an overkill for you but I thought I'll just point it out.
Cheers,
Vlad
Oh great thanks, it all works now again. i changed some code and tabelnames.
your system looks really nice, but indeed i think it would be overkill for my program. my current setup works now so thats fine.
So moving on:
-would it be possible to create some sort of error when a barcode is not found in my database? how would i do this or what is the term i need to search for online?
- can i add multiple barcodes per record? should i just add some colums with additional barcodes?
- sometimes i want to inactivate a record, so that it still is in the table but not active. Why? because sometimes the productbrand changes but it still is the same product.
so it would be great to have multiple products but only 1 active. example:
product 1 brand a several barcodes, active record, will scan correctly
product 1 brand b several barcodes inactive record, will give error when scanned
product 1 brand c several barcodes inactive record, will give error when scanned
product 1 brand d several barcodes inactive record, will give error when scanned
- is it possible to color a icon or something that will show green when scan is recognised and red when scan isnt recognised?
- i changed the date format of houdbaarheidorigineleverpakking to month and year only, as this is the only info displayed on the product (its the expiracy date).
is it possible to get a datepicker that only uses month and year? the datepicker in the form uses day month year as a format. (and if stored just use the first day of the month as a value)
thanks again!
you would need to use dlookup or dcount to look for the barcodes-would it be possible to create some sort of error when a barcode is not found in my database? how would i do this or what is the term i need to search for online?
I wouldn't add additional fields in the same table; instead create a new join table holiding ProductID|Barcode|Active(Yes/No)- can i add multiple barcodes per record? should i just add some colums with additional barcodes?
See above new table with new Active field (Yes/No)- sometimes i want to inactivate a record, so that it still is in the table but not active. Why? because sometimes the productbrand changes but it still is the same product.
so it would be great to have multiple products but only 1 active. example:
product 1 brand a several barcodes, active record, will scan correctly
product 1 brand b several barcodes inactive record, will give error when scanned
product 1 brand c several barcodes inactive record, will give error when scanned
product 1 brand d several barcodes inactive record, will give error when scanned
first question asks for error if not present - use the same dlookup formula as a conditional formating expression for the barcode field font color- is it possible to color a icon or something that will show green when scan is recognised and red when scan isnt recognised?
the date picker picks a date, there is no month picker. And you should keep it as a date (first of the month) in case you will need to do calculations (how many months to expiration date) - if you change it to month-year it will be harder. Simply apply Format("MMM-YYYY") to your expiration date field to present it as Jun-2018.i changed the date format of houdbaarheidorigineleverpakking to month and year only, as this is the only info displayed on the product (its the expiracy date).
is it possible to get a datepicker that only uses month and year? the datepicker in the form uses day month year as a format. (and if stored just use the first day of the month as a value)
I haven't touched your new db. Post back if you need help with any of these.
Cheers,
Vlad
for the dlookup i tried this after googling:
not only does it create a record anyway, it doesnt give the errors. And it saves the record either way even when barcodes dont matchCode:Private Sub Knop296_Click() BarcodeScanned = Nz(DLookup("Barcode", "Overzichtstabel", "Barcode= '" & Nz(Me.BarcodeScanned.Value) & "'")) If BarcodeScanned = "" Then MsgBox "Barcode niet gevonden!" End If If Not BarcodeScanned = "" Then MsgBox "Barcode gevonden" DoCmd.RunCommand acCmdSaveRecord End If End Sub
do i really need the NZ code by the way?
- ill leave the datepicker as it is, its functional enough now with the format i picked.
- Could you assist me with the join table? i dont really understand how to start. ive started looking at videos and tutorials but they arent very clear to me.
ive created a table using a make table query like this:
-do i need to link these records to the product id primary key from overzichtstabel?
-where do i enter additional barcodes for the same product/record?
-how do i add diferrent brands of the same product? (these different brands of the same product share a common code called a ''gpkcode'')
just to clarify:
1 product can have several barcodes, but it has 1 ''knmpnummer'' code
several brands can make these products, all with their own different ''knmpnummer'' code
1 these products of the same type and knmpnumber all share a ''gpkcode''
considering these are medicines, the gpkcode is essentially which medicine is prescribed, knmp number refers to the product of that specific brand. one brand can have different barcodes(sometimes they import medicine from another country and have different barcodes)
ok i fixed the dlookup code myself:
this works . EDIT: no it doesnt work... still makes a record even if incorrect barcode.Code:Private Sub Knop296_Click()BarcodeScanned = Nz(DLookup("Barcode", "Overzichtstabel", "Barcode= '" & Nz(Me.BarcodeScanned.Value) & "'")) If Not BarcodeScanned = "" Then MsgBox "Barcode gevonden" If Not BarcodeScanned = "" = True Then DoCmd.RunCommand acCmdSaveRecord Else MsgBox "Barcode niet gevonden!" End If End Sub
still struggling with the join table if anybody could help me please.
heres the db file: https://drive.google.com/open?id=1Rp...yLoe_UXL9LbjuD
Last edited by farmi; 06-20-2018 at 07:47 AM.
You are contradicting yourself when you say you don't want a new (not-existing barcode) not to be entered (create a new record) and at the same time color it red. I think you need to allow to enter it then open another form to add it to the new Barcodes table (in the new form add a combobox to select the knmpnummer).
I have modified your barcodes table to remove the extra fields. I also modified the append query (but not used it in the code) so you could see the new design. I changed the product form and table by removing the Barcode field from the table and replacing it on the form with a subform based on Barcode and linked by knmpnummer.
Hopefully these changes will give you enough info to see how the new design needs to be implemented. I noticed the form that checks the barcodescanned has some controls which are missing from its record source, you want to correct that.
Cheers,
Vlad
Ive thought about it and the red coloring is not necessary, as now i have a error message instead of the red color.
i just want to block entry of non existing barcodes in this form.
ill have a look at your adjustments.
thanks
the reason i want to block unrecognised barcodes is because i dont want the user to be able to enter them in the log. this way the user must first find an admin that will fill out a form with product info and barcode. after this the user can scan the barcode and log the product. the reason i want an admin in this proces is because the admin can check if the data is entered correctly because the admin is responsible for the log. Most users will be ''untrained'', so we wont allow them to enter new products/barcodes
i ditched the code i used previously and used a combobox for barcode entry
Agree, combo box is great for that.
Cheers,
Vlad
Could you please assist me on how to assign several barcodes per producttype and several identical products from different brands per producttype?
I could not see the gpkcode you mentined earlier in the database. Can you show in a prototype how are gpkcode and knmpnummer related. Have you looked at the form I modified to add the subform for the barcode? In your other form where you add the scanned barcode you need to add the knmpnummer and gpkcode in combo boxes and allow users to select the two to populate the rest of the info on the form. I can't really help you with the actual design as I can't work with your 2016 db.
Cheers,
Vlad
i dont see any of the changes you talked about, are you sure you have uploaded the correct version?I could not see the gpkcode you mentined earlier in the database. Can you show in a prototype how are gpkcode and knmpnummer related. Have you looked at the form I modified to add the subform for the barcode? In your other form where you add the scanned barcode you need to add the knmpnummer and gpkcode in combo boxes and allow users to select the two to populate the rest of the info on the form. I can't really help you with the actual design as I can't work with your 2016 db.
Cheers,
Vlad
here is a example of the data for the gpk code and related data. i have not made a gpk code colum in any table yet. i would probably would do this in the overzichtstabel, which is the main table of my database.
OK, so it looks like you have a one-to-many relationship between gpk and knmp and another one-to-many between knmp and barcode. You will need to add a joining table for the first one (you already made the Barcodes table for the second) where you would have UniqueID (autonumber, PK), GPKCode, KNMPNumber. The you need to review the existing tables and remove the duplicate fields. Finally you would use subforms linked by the appropriate fields to allow you to add multiple knmp numbers for any gpk code and multiple barcodes for any knmp.
I am attaching the file with the subform so you can see how it works.
Cheers,
Vlad