Syncing Combo boxes, which are also lookup values

Hey All,

So I have a bit of a problem. I have two tables,

tbl_Retainer
tbl_Retainer_Grant_Funding

tbl_Retainer has the field,

Retainer_ID

and tbl_Retainer_Grant_Funding has the fields,
Retainer_ID (a lookup field from tbl_Retainer)
Agreement_Num (a lookup field from tbl_Grant)

I have a form based off of a query(not sure if that matters), that uses that tables, tbl_Assignment and tbl_Assignment_Grant_Funding. These tables have the above fields as lookup fields.
So...what happens is, if an Assignment has a Retainer, I want the Agreement_Num box to show only the Agreement_Num's associated with that Retainer, otherwise just show all the Agreement Num's.

In my form, I have Retainer_ID with the row source,
SELECT tbl_RETAINER.Retainer_ID FROM tbl_RETAINER;

And Agreement_Num with the row source,
SELECT [tbl_GRANT].Grant_ID, [tbl_GRANT].Agreement_Num FROM tbl_GRANT ORDER BY [Agreement_Num];

in my AfterUpdate event for Retainer_ID I have,

Private Sub Retainer_ID_AfterUpdate()
Dim strSql As String
strSql = "SELECT [Retainer_ID]," & _
"[Agreement_Num]," & _
"FROM tbl_RETAINER_GRANT_FUNDING" & _
"WHERE [Retainer_ID] = " & Me.Retainer_ID.Value

Me.Agreement_Num.RowSource = strSql
Me.Agreement_Num.Requery
End Sub

When I am in my form and choose a Retainer ID, the Agreement_Num box goes blank, and there are no choices to choose from. I am wondering if this is because the Agreement_Num's are sourced from tbl_Grant and not from tbl_Retainer_Grant_Funding.

Can someone please help me with this issue??
Thanks so much

Syncing Combo boxes, which are also lookup values

How to get data from forms into a table

Hi All,

Quick question,

I have built the attached database and have an issue the getting data from the form into the table.

Tbl_data is the main table where all data is stored. Within Frm_data I have added 3 text boxes that pull the FL, FLM and area from tbl_staff depending on the name chosen in the name combo (combo 89).

My question is how do I then get the data auto populated from the FL, FLM and Area from frm_data into tbl_data so I can then pull this data into queries and reports.

If you select Abiola Sokoya from the name drop down it will auto populate FL, FLM and area as an example.

Hope this makes sense.

Regards

Attached Files

Copy of DG Database.zip (65.1 KB)

How to get data from forms into a table

Formatting Date within Do.Cmd.RunSQL

Hi All

I have a routine which keep putting in a date in the U.S. format and I am in the UK. I have tried to research and come up with a solution but still can't get it to work. Does anyone have any clues please?

Code:

---

Application.DoCmd.RunSQL "INSERT INTO temp_tbl_QBData([flicksfeedbackleft], [class], [qNAME], [EventID], [vatamount], [totalvat], [totalinv], [acct], [incomeAccount], [filmNameDescription], [today], ) VALUES

...lots of other fields here....


"#" & Format(.Fields("today").Value, "dd-mm-yyyy") & "#,"

---

Thanks for any help.

Formatting Date within Do.Cmd.RunSQL

looking up data from other tables

Hi,

I am trying to improve a current data base that is not been constructed brilliantly in the first place but working with it.

I have a form linked to a table (I am adding a second table to it) which has lots of data as massive tables full of time points and samples at specific time points.

I am creating a subsequent form that will pull certain data from the above form and table(s) and letting me add some at bits to produce a report.

One of the things that I want to add is looking up how many samples have been taken up to a certain time point and how many are left. But would prefer not to do the maths and can lead to error...

There can be up to 6 samples on each project, and will be 15 time points.
But not to sure how to specify which time point that I am using in the subsequent form to link to the correct field in the original table(s)

Anybody got any ideas?
been thinking of iif/lookups but not really too sure how these work tbh.

Thanks
Mikey

looking up data from other tables

count, vbLf, string

Hello Experts,
I have to count the number of occurrences of Line feed (vbLf) in a long string.
The code I am using is:

Code:

---





       

Code:


       

---

MAX = TLine.Split(vbLf).Length - 1

---

---

It does not work. Help please.

count, vbLf, string

running a query based upon a listbox selection

this is likely very easy but being somewhat new to access listboxes I am having trouble. I have a list box that is correctly listing resources from a table (tblResource). I would like to select one of the list box entry and run a query against the tblResource to show the information for that resource in a form. I have tried to use the lstindex with the control (lstResource) in the where clause of a query to accomplish this with no success. Any ideas for me?

running a query based upon a listbox selection

Delete records from a table

good day !

need assistance on below.

have two tables as follows;

Master file:Vessel_master
Vessel_code
Vessel_name

main data table:Main
Vessel_code
container_no
size
Voyage
weight

by using above two table have made a query as follows;
Vessel_name:Vessel_master
Vessel_code:Main
container_no:Main
size:Main
Voyage:Main
weight:Main

Have joined "vessel_code" fields of the both tables by join type 2.

by using the query as data source made a form to edit/update/delete data. the problem is, when delete a record by using the form, it will delete the related data in the master file as well, which i do not want.

Appreciate any expert's advise.

brgds
Htadis

Delete records from a table