VBA Code Help

[knodle01]

Good Morning Group,
I am new to VBA coding and had a question. I am building a database that contains several records that all contain specific individual codes. I have created it to return the new available code in the table but here is my problem. I have 2 check boxes. The first check box is an in use checkbox and the second is a used. I want to hide the in use checkbox and I want this checkbox to be checked or enabled by the system when the record is pulled. How do i do this?

Thanks in advance.

[MindBender77]

The first check box is an in use checkbox and the second is a used. I want to hide the in use checkbox and I want this checkbox to be checked or enabled by the system when the record is pulled.

There are a few ways of accomplishing this. One way is to set the "in use" checkbox visible property to False. Next, place the record your looking for into a textbox control then, add an IF statement to the OnChange event of that control if populated by a table/query. If the textbox is populated by a user then use the OnExit event.

Something like:
[code=vb]
IF Textbox1 = "some record" Then
CheckBox1.Visib le = True
CheckBox1="1"
Else
Checkbox1.Visib le = False
CheckBox1="0"
End IF
[/code]

This is untested but, hopefully points you in the right direction,
Bender

[knodle01]

There are a few ways of accomplishing this. One way is to set the "in use" checkbox visible property to False. Next, place the record your looking for into a textbox control then, add an IF statement to the OnChange event of that control if populated by a table/query. If the textbox is populated by a user then use the OnExit event.

Something like:
[code=vb]
IF Textbox1 = "some record" Then
CheckBox1.Visib le = True
CheckBox1="1"
Else
Checkbox1.Visib le = False
CheckBox1="0"
End IF
[/code]

This is untested but, hopefully points you in the right direction,
Bender

Thank you for your response. I did figure out how to hide the checkbox thanks to your suggestion. The issue I am now having is:
When the users selects the form I have it populate with the next available record in that table that is currently not in use. The field for the checkbox i want to automactically enable is called InUse. No what I am trying to do is everytime the specific record is pulled I want the InUse checkbox to enable that way the next user can pull the next record in line and so on and so forth. How would my code look for this? Thanks in advance.

[PianoMan64]

Thank you for your response. I did figure out how to hide the checkbox thanks to your suggestion. The issue I am now having is:
When the users selects the form I have it populate with the next available record in that table that is currently not in use. The field for the checkbox i want to automactically enable is called InUse. No what I am trying to do is everytime the specific record is pulled I want the InUse checkbox to enable that way the next user can pull the next record in line and so on and so forth. How would my code look for this? Thanks in advance.

What your going to need to do is put into the FORM CURRENT subroutine:

Private Sub Form_Current()
    If Me.InUse = True Then
        Me.InUse.Visible = True
    Else
        Me.InUse.Visible = False
    End If
End Sub

[knodle01]

Thanks PianoMan64 for your info. I posted this into the before update event procedure and it is still not enabling the InUse checkbox when the record is pulled from the report. I am inserting this into the incorrect field? I def. want the code to enable the invisible checkbox so the form will pull the next record that isnt being used.

Thanks in advance.

[PianoMan64]

Thanks PianoMan64 for your info. I posted this into the before update event procedure and it is still not enabling the InUse checkbox when the record is pulled from the report. I am inserting this into the incorrect field? I def. want the code to enable the invisible checkbox so the form will pull the next record that isnt being used.

Thanks in advance.

Then, if that's not it, can you explain in more detail, keeping in mind that I know nothing of your project and how it is setup.

I need to know what field you're speaking of, and the name of it, and how you're wanting the record point to work.

1. Are you wanting to Move the record pointer to the a new record to another another entry?
2. If yes, then what is the purpose of InUse, and how is it that you're wanting to respond or work?

I just need some kind of guidence as to what it is that you're trying to do.

Thanks,

Joe P.

[knodle01]

Then, if that's not it, can you explain in more detail, keeping in mind that I know nothing of your project and how it is setup.

I need to know what field you're speaking of, and the name of it, and how you're wanting the record point to work.

1. Are you wanting to Move the record pointer to the a new record to another another entry?
2. If yes, then what is the purpose of InUse, and how is it that you're wanting to respond or work?

I just need some kind of guidence as to what it is that you're trying to do.

Thanks,

Joe P.

I will try and explain in as much detail as I can. Thanks for the assistance.

I have a db that has a table with 3 fields. The Unique code feild which will have the codes I want the users to be able to view when they pull the first record available. The InUse field which I want to enable automatically as soon as one of the records is pulled. And a Used field so that when someone uses one of the records it wont be pulled by the next user looking for a code. I have been able to make the form pull the first record that doesnt have the InUse checkbox or the Used checkbox enabled. I can manually enable the checkboxs and the form will pull the next record. I want to make the InUse checkbox checked as soon as the record is pulled so that multiple users wont pull the same record at the same time. Does that make sense? Look forward to your response

[MindBender77]

This is just a thought but, I don't believe Access will not allow multiple users to "view" or open the same record. Only one person can view/open a specific record from a table at a time.

I could be wrong, however the experts here could probably clarify this theory, as it were.

Bender

[knodle01]

Good Afternoon,
I dont beleive that is will allow mulitple users to view records but my form currently only pulls the record because I just need the user to see the 10 digit id from the record label. Is it possible for Access to automatically enable a checkbox through the use of VBA code? I have never done this before.

Thanks!

[PianoMan64]

I will try and explain in as much detail as I can. Thanks for the assistance.

I have a db that has a table with 3 fields. The Unique code feild which will have the codes I want the users to be able to view when they pull the first record available. The InUse field which I want to enable automatically as soon as one of the records is pulled. And a Used field so that when someone uses one of the records it wont be pulled by the next user looking for a code. I have been able to make the form pull the first record that doesnt have the InUse checkbox or the Used checkbox enabled. I can manually enable the checkboxs and the form will pull the next record. I want to make the InUse checkbox checked as soon as the record is pulled so that multiple users wont pull the same record at the same time. Does that make sense? Look forward to your response

Hey knodle01,

If the solution that I've provided below doesn't do what it is that you want, then I'm going to need a lot more detail as to table structure, field names, relationships between tables. what the names of the controls on the form.

What you've given to me so far is a very small peace to the over problem that I see you're trying to resolve.

What I would like beside all the detail, is to have you explain:

1.) What is it that you have so far that works?
2.) What you want to have happen when a user is entering data?
3.) What is the end result that you're looking to find?

I do have some idea as to what it is that you're trying to do and the example below explains how to do that. But I think, you're also asking to have this combo box be dynamic across multiple users? And that is a great deal of a bigger problem and that is why I need as much detail as you can provide.

I'll explain it like this, imaging someone has to explain to you how you've built every single Screen, Control, Form, report, query and table in the entier database. The reason that I say that is, because if you know nothing about what you've done, of what you know, and what you're capable of doing yourself, and what things we can do to help you learn some things along the way, that's what we're here for.


From the sounds of what you're talking about, is sounds like you want to limit the items displayed in a combo list box?

If that is the case, then you need to simply change the query that is populating the Combo Box control.

You would simply add a condition in there that says that InUse must be False. Then only those items are selected.

Now if the form that you're entering the data on, also updates that combo box list dynamicly, the question that I have is, at what point is the item to be considered Selected and/or InUse? this is going to be a key point, if this is a multi-user application? This is something that I need to know in order to give you the best possible solution for this.

If you need coding assistance with how to make what i've explained, the following need to be changed in the Combox Box Control

Lets assume for a moment that your list box control name is cboAvailableIte ms

and you have the following fields in the table:

Tablename: AvailableList
------------------------------------
ID        AutoNumber          Primary Key
InUse         Yes/No
Description  Text(50)

Now in the combo box control named cboAvailableIte ms, in the Row Source field the following example would go into the field. You can also click the ... button on the right and use the query builder to build your query.

SELECT AvailableList.ID, AvailableList.InUse, AvailableList.Description FROM AvailableList WHERE (((AvailableList.InUse)=False));

I've also attached a simple database example as a zip file, that you can use as a working example of how to filter a combo box based on the InUse field being true or false.

I hope this answers your question.

Joe P.

[knodle01]

Thank you for all your patience PianoMan. I would be happy to send you my access file that I have so far so that it will be easier for me to show you what I want to happen. I dont seem to be able to upload it onto the site. It isnt very large. I can send it to you if you wanted to private message me an email address to send it to. Thanks again.

David

[Stewart Ross]

Hi Knodle01. This is Joe's call here, but I notice that Joe has already provided detailed responses for you as well as a sample database. We do expect that you put some time and effort in for yourself - otherwise you will not learn how to resolve difficulties for yourself.

When learning about Access there is no 'quick fix' way to overcome what at times seems like a blockage - and getting one of us to do the work for you is not an appropriate way to handle such problems. Joe has asked some quite specific questions in the way of eliciting information from you about your database. You have not answered his questions, and expecting him to deduce the answers by looking at your database instead is not really a substitute.

If Joe agrees to look at your DB that is fine - but in normal circumstances trying to sort out your DB itself would really be a last resort. It can be very time consuming to do so, and Joe like all of us is volunteering his time freely to help others.

Attachments can be placed onto your post within an hour of posting, by choosing Edit, Manage Attachments. A database would need to be zipped before attachment.

Joe may choose to decline the offer of looking at your DB - as I say that is his call to make.

-Stewart