My Combobox technique is falling short.

Question

I am sure this is a very basic lack of thought/knowledge on my part. It's a long time since I did any code.

So here goes. I'm recording data about my ancestors I have a single user database in Access with one main table and 4 other tables, one of which is called "Concerns" and is a 2 column table with autoID numbers and short names e.g. 10,WilliamT-2. Concerning: ID Autonumber, Firm (short text) The main table has a form which has various boxes including a combo box which refers to "Concerns". I have now started recording ledger entries from 1837-1850 and the Concerns field will be the same in all of them ( No 10,Wm2). So, if there is a page reference in the box "Ledger page", I would like, inter alia, to set the Concerns box to 10 programmatically without having to do it myself for the next several hundred records.

Herewith my simple code, which will set a value in an ordinary text box, but will not set it for "Concerns", giving error 3032

Private Sub Ledger_page_Exit(Cancel As Integer) Concerns.SetFocus If Ledger_page.Value <> 0 Then Let Concerns.Value = 10 Else Let Concerns.value=Null

End Sub

I am trying to set the value of Concerns to numeric 10- corresponding to Wm2, firstly in the form and thence into the table itembasic. I am not trying to add a record. That way when I go back to the record in the form, it will show up as WilliamT-2. However, I can't make it work, probably because the datasouce is from the table "Concerning" and not set to receive anything else.

I can't find the solution after several hours- so I have to ask! No doubt I have omitted something vital.

Screenshots of the fields of Itembasic (main table) and both form and design modes for the Item1 form will be appended as soon as I am verifiedhanks

Mike

Answer 1

If Me.Concerns<>0 Then

Me.Concerns=10

Else

me.Concerns=0       '(Null is not a valid value in this case)

End If

Further, if you want to do a whole group of field changes at one time, then use an Update action query to set all the Concerns field values to 10 all at one time. Then go on from there using your form.

Answer 2

An update query would change all your records at once

If I've understood you correctly, you have a table Concerns with a field also called Concerns and you want to set the field value=10 where it isn't currently zero. 

If so, then you can use:

UPDATE Concerns SET Concerns.Concerns =10 WHERE Concerns.Concerns<>0;

Double check that is what you want as I didn't understand the Page_Ledger part of your post

I would also recommend changing the table name to e.g. tblConcerns to avoid confusion

Answer 3

OK - to ensure no further confusion, please can you show a screenshot of the design of each table and any relationship between them

Answer 4

I would have done that at the beginning, but the forum will not allow me to post screenshots or attachements until I have registered. Although I think I have been accepted it still wont so I will try again in the UK morning - it would indeed be so much easier to explain with the shots. Sorry for that.

Mike

Answer 5

Hi Mike

You are certainly a registered member. Perhaps you have to do a few posts before it allows screenshots?

Anyway I'll wait till tomorrow - I'm also UK based

Answer 6

I would have done that at the beginning, but the forum will not allow me to post screenshots or attachements until I have registered. Although I think I have been accepted it still wont so I will try again in the UK morning - it would indeed be so much easier to explain with the shots. Sorry for that.

Mike

Hi Mike,

I don't know for sure, but perhaps that is because you still have 0 points.

I will vote for you, so you will haveat least 5 points. Hope it helps ...

Imb.

Answer 7

Mike

I just read your last post again carefully & I now believe the update query only involves one table - itembasic

UPDATE ItemBasic SET ItemBasic.Concerns = 10
WHERE (((ItemBasic.LedgerPage) Is Not Null));

However if for any reason you need to reference both tables then try

UPDATE ItemBasic INNER JOIN Concerning ON (ItemBasic.Concerns = Concerning.ID) AND (ItemBasic.Concerns = Concerning.ID) SET ItemBasic.Concerns = 10
WHERE (((ItemBasic.LedgerPage) Is Not Null));

BTW - do your tables each have a primary key field?

Answer 8

I would have done that at the beginning, but the forum will not allow me to post screenshots or attachements until I have registered. Although I think I have been accepted it still wont so I will try again in the UK morning - it would indeed be so much easier to explain with the shots. Sorry for that.

Mike

Post a request to verify your account to this thread - https://social.msdn.microsoft.com/Forums/en-US/94f05325-8566-4c4c-806c-179a5a0beafc/verify-accounts-43?forum=reportabug

Answer 9

Thanks lmb

Answer 10

Thanks RLWA, I've done that. What a performance.

Now I'll try again to send the screenshots

no- not allowed, so use dropbox

https://www.dropbox.com/s/4qksailco1sduct/36853-image.png?dl=0

https://www.dropbox.com/s/8luq5w8wt5nu8pz/36827-image.png?dl=0

https://www.dropbox.com/s/4k8et7n6fji0sga/36871-image.png?dl=0

That seems to work

Thanks everyone

Mike

Answer 11

Morning isladogs

Yes, both tables have a primary key field- I remembered that much!

See screenshots below

Mike

Answer 12

Hi Mike

I looked at your Dropbox images but can only see the table design for itembasic.

Did you try either of the update queries I suggested?

Answer 13

Herewith table design for Concerning

https://www.dropbox.com/s/pvc5u6pllmeh6xh/2020-11-05_151557%20copy.jpg?dl=0

Got lurked to mend a cupboard- will have to try later if I can remember how 

Answer 14

Remember how to mend a cupboard??? Or how to create an update query.

The latest screenshot  shows the data but not the structure.

However, if its just those two fields, the structure was given in an earlier post.

I'm still not clear if there is a relationship between the two tables or whether there is any need to use both tables in your query

Answer 15

Ho Ho

It is just these two fields

I'm not at all set on either queries or using both tables.

When I enter data using the Form, as I pass from field to field, if the entry comes from this 75mm thick ledger, then I enter a ledger page value e.g. 44 or if there are more than one per page, 44.1 etc.

If it is not data from the ledger , then I use the other page value higher up in the form and there is no entry in the ledgerpage box.

I'm sorry if that is confused/bad practice etc., but it evolved as I accumulated more information.

All I want to do is that if when I leave the Form.ledgerpage box and it has a value <>"" then I make the Concerns box to say WmT2. Does it need a query to do that- I am totally ignorant on this

Thanks

Mike

ps I'm ok on cupboards

Answer 16

No you don't need a query to do that.

1. Open the Item1 form in design view
2. Select the ledgerpage control box
3. Click the Event tab on the property sheet
4. Click the down-arrow on the After Update event
5. Select Event Procedure
6. Click the 3 little ... symbols to open the VBA window
7. Type the following VBA code:

If IsNull(Me.ledgerpage)=False Then

Me.Concerns="WmT2"

End If

So after you update the ledgerpage field on the form, if the ledgerpage field is not null, then WmT2 is entered in the Concerns field.

Answer 17

Don't you still want to set the concerns field to 10 where ledgerpage is null?

That was the reason for the update query....

Answer 18

No, Isladogs, I'm sorry to have misled you. It's only when the entry comes from the Ledger- i.e. there is a ledger page entry, that the Concerns should be 10 form Wm2

Lawrence

I've tried your code for which thanks, but it hangs up. I have tried entering Me.concerns.value as well and going from the alphabetic to numeric WmT2 to 10, but get this

https://www.dropbox.com/s/490jxomw1y3szmr/2020-11-05_2100.jpg?dl=0

Answer 19

Sorry - I meant where it is NOT null.

Unable to read the relevant part of your screenshot. When I increase the zoom level, it is blurred.

In future, please crop & just show what is needed.

Answer 20

When I looked at your screen shots, the Concerns field is on the Item1 form, not the Item form and the control name on that form is Ledgerpage. The code will not work on the Item form, only the Item1 form.

Answer 21

Sorry about that. Will do

Answer 22

You are correct. The "Item" form was my first shot and I have discarded it to simplify things.

Answer 23

Herewith more or less the same screen shot but highlighted. I have explored several variations on the theme but to no effect.

https://www.dropbox.com/s/hrnwjaglgqxf6lx/debug1-image.png?dl=0

Answer 24

You are still posting a picture of the Item form. I thought you said you got rid of it?

1. Open the Item1 form in design mode
2. Select the text box control that has LedgerPage as its control source
3. Make sure the Name property is LedgerPage as well as the control source property (in this instance, they should be the same)
4. Insert the code I gave you with the LedgerPage Exit Event. When another control is clicked then the code will put WmT2 in the Concerns field if LedgerPage is not null.

Is that what you wanted? I am still a little confused on what exactly you want to happen and when.

Answer 25

Lawrence

Sorry to be so long getting back

In order- I've created vast confusion by naming, in the distant past, the title of the Item1 form as Item. Sincere apologies. I have now renamed it again but appropriately!

1 and 2 Done

3. Tidied up the naming of the control so name and control source both read LedgerPage

4. Done- except that I used shorthand and confused you by putting WmT-2 when I meant WilliamT-2. However, you have perfectly understood what I want and wanted to do, by the code filling in the value for me if I had entered a value for LedgerPage.

All that is fine, but I get an error message as attached shot. I have tried concerns.setfocus but that just gives an error saying that it is read-only. I've looked at reading from a list of values, but it will still be read-only (I think)

The error message on the attached was Runtime error-2147352567(8002009). Cannot perform this operation.

Link to Dropbox https://www.dropbox.com/s/kl1qfpp7b4mw29t/7Nov%20error.png?dl=0

Thanks

Mike

Answer 26

Look at the properties for the Concerns dropdown box. It appears to have a label called "Involves" (that's OK). Make sure the default value property is blank and the Enabled property is set to yes and the Locked property is set to No and the Limit To List property is set to No.

In other words, make sure the Concerns field is editable. While you at it, you might also make sure the Record Source for the form is editable. Open the Record Source and make sure new records can be added.

Answer 27

Lawrence

Thanks. As far as I can see all the bits are in place. Can't see why the Concerning list needs to expand, but there you are.

I put up a link to the whole thing if you have the patience- may save trouble. There is nothing there of financial significance after this time.

Link removed.

lMike

Answer 28

Mike:

I downloaded it and am reviewing the entire file. It's a mess. If you want the concerns field to be able to be text, it has to be a Datatype Text in the table. Currently, you have it Datatype Number, it's no wonder it won't accept text when after LedgerPage is updated. I'll try to fix it. Also, you have no relationships between tables established. I will try to get back to you tomorrow.

Answer 29

Lawrence

Sorry it's a mess. In my defence, I used the wizard to provide the concerns combo box- I thought it stored the number and then looked up the text to display it. Anyway, it's very kind of you to look at it. I will delete the link for now. However, there is no panic.

Answer 30

It's OK, Microsoft should have never left the option to use a lookup combo-box as a table field format. Never. It should be deleted as an option. It never worked. Anyway, that's not your fault. It appears you want to just keep information about relatives. So you can have several relatives, each could have had multiple workplaces (you call them concerns) with a Firm name.  Each could also have multiple other details which you apparently getting from some log or history document.

I'll reconstruct it in proper relational design and copy it to my OneDrive directory. You can download it from there when I give you the link.

Answer 31

It's even a bit simpler than that.

Each item in itembasic has a link to one of the Concerns values- or none if I don't want it.

Ditto Sourced from- so I can get all the entries from Gore Gazetteer, for example.

Associated people was one I started with but in fact hasn't been used but will come in more later

Again, all these secondary table values are optional not mandatory.

It all started with an argument between me and the Institution of Civil Engineers as to whether John-1 was a quarryman or a contractor, so I started gathering info on a spreadsheet and then realised that there was some significant historical stuff that could be of wider interest.

Many thanks for all your hard work.

Answer 32

Mike:

You can download the file here:

https://onedrive.live.com/?id=9981FDC9E7DC7D83%21284&cid=9981FDC9E7DC7D83

I couldn't translate everything over so you will need to go down through each record and select a Relative Name and/or Concern and /or Source. The History Log form opens automatically when the file is opened. From there you can open each of the other forms too. It is probably as close to a relational model as its going to get. You can see the relationships in Database Tools>>Relationships.

Answer 33

Lawrence

You must have done an awful lot of work on this and I'm very appreciative.

I can't find the code that sets the value of Concerns to 10/WilliamT-2 if there is a value in the ledgerpage box. Could you enlighten me please?

Thanks again

Mike

Answer 34

Copy the following following entire Sub to the History Form VBA window. Sorry, I didn't know if you still wanted that to happen. Also, if you don't really need to see the ID numbers, you can just set their visible properties to 'No'.

Private Sub Page_reference_Exit(Cancel As Integer)
If IsNull(Me.Pagereference) = False Then
    Me.Concern = "WilliamT-2"
    Me.ConcernID = DLookup("[ConcernID]", "Concerning", "[Firm]=Forms![HistoryForm]![Concern]")
End If
Exit Sub
End Sub

Answer 35

Lawrence

I had a reply already done- but it vanished when I pressed the Answer button

Many thanks again for the total solution which worked first time and is exactly what I wanted. I just need to understand it so I could use it again in the future!! The more I look- the more I see what a lot of hard work you did put in. Words fail me.

Grateful Mike