"You cant create new record"

[abusaif]

Good Morning.
I have developed a rental database. Every machine has a main form and a subform for rental details. Now what I want to do is that in subform if the machine is not returned (ie. field value is returned), then new record in the same subform for same machine would not be permitted, for example a message box "You Cannot Create new record".

Is it possible?

[burrina]

Something along those lines may be what you are after, however I do not know the data types of your fields so it is just a guess! Provide more details as to field data types and primary key,etc...

If Me.Returned = False And ID = ID And Me.[NewRecord] = True Then
MsgBox " You Cannot Create a new Record"
Me.Undo
Exit Sub
End If

[abusaif]

Here is the db. It will be cleared now that "when on subform field name [maintenance status] is empty (null) then new record underneath that would not be created".

Feel free to ask details.

[burrina]

If not, let me know!

[abusaif]

I am not very good with VB. But let me give you a clue.

if (me.maintstauts <> "return") then
me.newrecord = false
else
me.newrecord = true
end if

I want something like this
Actually nullity of [maintstatus] field means that the machine is still on rent and cant be rented to any other site. It must be filled to make further transactions.
I hope you get my point

[burrina]

So what I did will not work for you? Then I don't know what you want since it did accomplish the purpose. You want something else? Describe in detail if that is the case.

[abusaif]

You reached too close but I think there was a mistake in my explaination. Now I attached a Snapshot with a little explaination I hope it would work.

[burrina]

You got what you asked for. You can't create a record when NO MaintStatus IsNull. If you hit F5 or refresh you can edit/change the first record and then when it has data you can then add data to the next record, which is what you asked for. If it is not , then I don't think I can help you. I spent a considerable amount of time on this. Try what I asked and see if it will work for you, if not, then I must bow out and maybe someone else may be able to help you that better understands what you want. first record is available, but not the next one until you enter all of the data, then do a refresh and then second record will be available and so on.

[digitalbalu]

Its better You re design your forms with slight changes. Separate New Machine Entry and its rental. Use a combo box to select a machine. In the combo box control source use the query which shows only the available Machines. I will re design your form .

[burrina]

Then I bow out and you can take over. I don't co-design for anyone.

Good Luck With Your Project.

[digitalbalu]

Before that plz answer for my understanding.

If the returned date is some xx/xx/xx and maintenance status is returned then the machine is ready for rent. Am I correct?

[digitalbalu]

Then I bow out and you can take over. I don't co-design for anyone.

Good Luck With Your Project.

Oh! burrina. I am extremly sorry. All the above reply is for abusaif not for you. I forget to mention his name. Still I am a novice.