Problem with Dcount function

[hardik_088]

Hi,

I solved type mismatch error problem but i get new problem while doing that. Here I attach my database . In the "Insert_data" form you try record 20 from laptops table and enter data in this fields date,type,supplier , description,location,department,part number
here if i enter different value in department field then it will insert query but if i enter any different value from table in department then it is still updating record why?

Actually I want to check that if description, location,part number,department is same from record then it should update query if one of them is different then it should insert query.


Thanks a lot

 

[GinaWhipp]

Why are you doing it this way? Seems more complicated then it has ro be. Just have a Combo Box to look up the laptop and if not in the list use the Not_In_List event of the combo box to add a new record.

Your way if there's an extra space it will run the UPDATE query...

 

[hardik_088]

Hi ,
My code can be complicated beacuse i am new for MS Access. So whatever i know I tried to apply that. So what I should do can you tell me little in detail.

Thanks a lot

 

[hardik_088]

how to use Not_In_List event of combo box and here in which combo box i should a apply so it is possible to update or insert data.

thanks a lot

 

[GinaWhipp]

Have a look at…
http://www.dataschenk.com/newsletters/Tips/Access_Not_in_List.htm

 

[hardik_088]

Hi,
actually I dont want to check just one combo box I want to check whole record if whole record is match to data in form that i enter means i can say if record exist then it should update otherwise insert. not to check just one com box

Thanks a lot

 

[GinaWhipp]

You would be checking the one combo box but I see you want to do it your way...

Let me take a look to see what's happening with your code. (As a side note, it was not the code that is complicated, to me it seems doing it with an UNBOUND form is complicated but it works for you.)

 

[hardik_088]

Hi,
you can try into database then please help me if you find any solution.


Thanks a lot

 

[GinaWhipp]

Okay, help me to understand...

What is...

.Value & Chr(34) & "," & Chr(34) &

...all that for?

 

[GinaWhipp]

In the meantime, I don't understand...

If I enter a different value vale the form it APPENDS, if I change the value it UPDATES. Are you saying if you change the value at the Table Level it's not doing what it's suppose to?

 

[hardik_088]

& chr(34) & [form.combobox_name].value &
it takes the exact value from the form and chr(34) is used because there is Text datatype.

there is [quantity ordered] field and i want to count the stock so if there is suppose same type of laptop available in table records then it add the value of [ordered quantity] ex... if you enter same data as record 20 from laptop table then actually table have quantity 4 if you enter that same data then it will add 1 more laptop in record means now we have 5 from 4 it added 1 laptops ([ordered quantity] have default value 1 you can change that.). I want to just eliniminate same record but it should added quantity. and if you change suppose department value then it becomes different record so it will insert new data in table that you enter in form.


Thanks a lot

 

[GinaWhipp]

Okay then what you want is if there is a different quantity to UPDATE that and not add to it? Because for me it adds to the 4, so if I cahngwe the Quantity to 100 I get 104 since all the other field match. Are you saying you just want the 100?

 

[hardik_088]

Hi ,
Ya you are perfectly right. If any field does not match then it should insert new data and there should be quantity 100 otherwise 104.



Thanks a lot

 

[GinaWhipp]

Okay so you are saying that is NOT what happens when you do it?

 

[hardik_088]

Hi,
ya i tried it many times but it did not work. if I change any field value like location or department then it is not working. suppose i enter record 20 from table so there is location value is "AHR" and also location is "PCH" or "NLH" is available in different records in table so if i enter this three values then it is updating but it should not.it should update with just "AHR" value not other values.What is the problem i am not getting?

Thanks a lot

 

[boblarson]

Your database is not designed properly. You should NOT have a separate table for laptops, a separate table for peripherals, etc. So this is going to be a real pain to maintain and to make meaningful. So, this is the last I will contribute unless you rewrite it to be normalized and not have separate tables for everything. It is just too much of a pain to work with.

But here's the corrected DCount for what you originally asked for:

            If DCount("Description", "Laptops", "Description = " & Chr(34) & Forms!Insert_data.description _
                                              & Chr(34) & " And [Part number] = " & Chr(34) & Forms!Insert_data.[part number] & Chr(34) _
                                              & " And [department] = " & Chr(34) & Forms!Insert_data.[Department] & Chr(34)) > 0 Then