Unbound box needs changing

[MrMcFestoe]

Need help with VB, i have a unbound field on my main form and have only just found out that its not updating my table. Now i need to change it to a bound box and have tried to put the following in as the control source

=DMax("[Date of Visit]","[Service History]","ID = " & [Forms]![Customers]![ID] & " and [Reason For Visit] = 'Service'&quot

This works for a unbound box but not for a bound box, i think it needs to be changed in to VB but iam struggling, any ideas.

thanks

 

[willir]

MrMcFestoe

Naaa. Although to do this at the code level is not too hard, you may end up in a write conflict with the current form.

A much easier way in the form design mode. Select the text field in question. If the property tab is not open, right click and select it. Move to the Data property tab. Select the data field in question for the control source.

Richard

 

[MrMcFestoe]

Richard,

Thanks for the replie sooo quick but the above DMax is pulling tha last date from a sub form would that still work?

 

[willir]

MrMcFestoe

Okay, so you got the one icon bashing the other on the head. I assume that is you telling me that I am "idiot" - yep, missed that one.

There may be a more elequent solution, but this should work.

Create a hidden text field that is bond to the control source in question. I will call it HiddenText for this example.

Assign it the value in the DMax value from the form. The way to do this is...

After the update event for the DMax text field, call it DMaxCalc, write the following code. Since I do not know what event you calculate the DMax value - curernt record, command button, data entry, the specific update event will need to be determined by you.

[blue]
if not isnull(Me.DMaxCalc) then Me.HiddenText = Me.DMaxCalc
[/blue]

The bound text box does not have to be hidden of course, but it just look funny sometimes when you see the same value. Also, what will you do if the bound field already has an existing number. Hmmm...
Richard

 

[MrMcFestoe]

Richard,

No the bashing icon is me! could this problem not be kept simple, i have a field called LASTSERVICEVISIT it works well with

=DMax("[Date of Visit]","[Service History]","ID = " & [Forms]![Customers]![ID] & " and [Reason For Visit] = 'Service'&quot

In the control source, but if i make a bound text box and put the above in the control source is does not work, Why ? i dont know. Could i not have a bound text box and put the above in the OnCurrent event?

Still cant understnd why the above wont work in a bound box.

Thanks.

 

[MrMcFestoe]

Richard,

Forgot, about you asking if a bond box already has a number, it changes every time a service call to a customer is made so it will have a date in and will change.

 

[willir]

MrMcFestoe

Sure, perhaps we can do it the other way around.

After the update event for the call, try adding the code...

Me.LASTSERVICEVISIT = DMax("[Date of Visit]","[Service History]","ID = " & [Forms]![Customers]![ID] & " and [Reason For Visit] = 'Service'&quot

Worse case scenario would be to add a command button to assign the new value. But you want to keep it simple, so tie it to an event that updates the DMax value. Perhaps Before Update??

The control source would be linked / bound to your table.
The DMax would calculate the new value

 

[MrMcFestoe]

Willir,

Tried your post but it just leaves the filed blank, is this because iam getting the date form a sub form?

Now its getting more of a puzzle.

But this i intend to beat.

Any ideas

 

[willir]

MrMcFestoe

Okay,

We know the DMax works -- you stated that it displayed but did not update the table -- correct?

Displaying a value to a form is pretty straight forward.

So I suspect we just have a minor glitch to work out.

So some basic questions would be...
- Is the code being executed? Put a "Stop" statement above the [blue]Me.LASTSERVICEVISIT[/blue] statement.
- Are the names of the form correct?
- Is the correct event being used?

Type of thing. (I wish I was looking over your shoulder -- sure would be a heck of a lot easier)

While you work on these issues, I will set soemthing up on my end to test.

Richard

 

[MrMcFestoe]

Willir

Not sure what you mean by put a "stop" Statement above the last service visit.

Just to just complicate things, if i leave the field as a unbound box then, i cant run a query on this information can i ?

I need to be able to run a report, and the report looks at the last service date if it is less than the service freq which is a bound box and could be equal to 12,6 or 3 months.

Sticking with this to the end.