VBA Validation

[brucey54]

Hi folks, changing my code slightly to take into account a combo selection on a form, but it is not working!

Basically trying to validate if the order query has been executed by checking if the date And Restaurant name selected has been already added to the order table.

If DCount("*", "TblDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & " AND (ComboSelectRestaurant = 'Watersidel'") <> 0 Then

 

[pr2-eugin]

Should your code be not this?

If Me.ComboSelectRestaurant = 'Watersidel' Then
    If DCount("*", "TblDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#")) <> 0 Then

 

[brucey54]

Just what I was looking for, cheers Paul

 

[pr2-eugin]

No problemo ! Good luck

 

[brucey54]

Paul having a wee problem, I have number of restaurants so

If Me.ComboSelectRestaurant = 'Watersidel' Then

Yep this is will work, and will allow the order query to run and reorder the stock, if the txtCusDate does not exist in the order table, however if the Restaurant name changes and I need to run the order query again for another restaurant, the order query will not run as the date already exist in the order table.

I think I need the code to check if restaurant name AND date already exist in the order table if not run order query.

I hope this make sense.

Brucey

 

[pr2-eugin]

Could you provide the field names in the order tables? What is DietPlan table? Why would there be no date?

 

[brucey54]

Hi Paul, this is a bit of a weird one, basically they is a TblDietPlan and TblRestaurant.

I’m trying to check if the restaurant has not already reordered by checking if the Restaurant date does not exist in the TblDietPlan already.
The code works ok but if I change the name of the restaurant, it will not work as the code will check to see if this date already exists in the table and if so will then execute the else part of the statement

If Me.ComboSelectRestaurant = 'Watersidel' Then
If DCount("*", "TblDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#")) <> 0 Then

maybe something like this

If Me.ComboSelectRestaurant = "Waterside" Then
If DCount("*", "QryPrintDataVBA", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & " AND [Restaurant] = 'Waterside'") <> 0 Then

 

[pr2-eugin]

If you mentioned that you had a column for the Restaurant in the table it could be simpler. However, try this.

If DCount("*", "QryPrintDataVBA", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & _
                                  " AND [Restaurant] = '" & Me.ComboSelectRestaurant & "'") <> 0 Then

You do not need the Previous If.

 

[brucey54]

Hi Paul, it worked great, thanks,

changing the subject here, when you create a form and click on a new record, the form create a new record,
easy peasy lemon squeezy

But what if you had to write the sql code yourself, do you have an example of this or a link to a good tutorial, thanks again for your help much appreciated.

Brucey